Question

For the given database schema. Answer the following questions.

Company Database customer(cust_id, name, address)

product(product_id, product_name, price, quantity)

transaction(trans_id, cust_id, time_date)

product_trans(product_id, trans_id)

Identify the primary keys and foreign keys for the relations and specify at least two different types of integrity constraints that would be applicable for different relations given.

Answer 1

given the following relations

1) customer(cust_id, name, address)

          - > primary key is cust_id

2) product(product_id, product_name, price, quantity)

          - > primary key is product_id

3) transaction(trans_id, cust_id, time_date)

           - > primary key is trans_id

        -> foreign key is cust_id referencing cust_id of relation customer

4) product_trans(product_id, trans_id)

           -> primary key is trans_id +product_id , it s a composit key

           -> both are foreign keys

                        foreign key 1 is, trans_id referencing trans_id of relation transaction

                        foreign key 2 is, product_id referencing product_id of relation product

------------------------------------------------------------------------

different integrity constraints applcable on the above relation are

1. Domain constraints ( definition of valid set of value for an attribute eg:- int , char , .... datatype )

2. Entity integrity constraints ( primary key value can't be null. )

3. Referential Integrity Constraints ( foreign keys can be used )

4.Key constraints( A primary key should contain unique value)