Database Management: A Referential Integrity Constraint (Essay Sample)

DATABASE SCHEMA
NAME OF STUDENT
NAME OF SCHOOL
SUBMITTED TO
DATE
Products (id: integer, title: string, price: float, quantity: integer, category: string, supplier: string)
Customers (id: integer, cname: string, address: string, telephone: string, email: string)
Suppliers (sid: integer, sup_name: string, address: string, city: string, province: string, postal_code: string)
Orders (order_id, integer, cust_id: integer, order_date: string, order_status: string, total: float)
ItemsPurchased (order_id: integer, product_id: integer, quantity: integer, price: float, total: float)
* Select a primary key(s) for each of these relations
Products - id
Customers - id
Suppliers - sid
Orders - order_id
ItemsPurchased- order_id
* Is the condition (id, cname) a key for the Customer relation?
Yes. The “id” acts as the primary key for the relation. However, the “cname” could have duplicates and can therefore be viewed as a alternative or secondary key.
* Is the condition (id, cname) a candidate key for the Customer relation?
Yes. The condition can be used to uniquely identify any tuple without referring to any other within the relation.
* Define all possible foreign keys for the relations.
Products – id, supplier
Customer – id
Supplier – sid
Orders – cust_id
ItemsPurchased – order_id, product_id
* Give an example of a tuple that the DBMS would reject because it would violate a uniqueness constraint
Relation: product

id

title

price

quantity

category

supplier

1

1254

machinery

3000.00

200

H.G

Donald

2

1254

machinery

3000.00

200

H.G

Donald

* Give an example of a tuple that the DBMS would reject because it would violate a referential integrity constraint
Foreign key (Product_Id)
Relation 1: Items Purchased

Order_id

Product_id

quantity

Price

total

1

466559

1254

70

350

800

2

466255

1448
<...