SQL database definition

A retail chain owns several stores. Each store, depending on its sizeand the range of products it stocks, is designated as being either a regionalor a local store. Each employee works at a single store. Some employees maymanage a store.

Entity-relationship diagram

Please see attached

Entity types

Employee (EmployeeNo, Name)

Store (StoreCode, Type)

Product (ProductCode, Description,Cost)

Additional constraints

c.1The EmployeeNo attribute of Employee takes a character string value in therange 00000 to 99999.

c.2The StoreCode attribute of Store takes a character string value in the range 00000 to 99999.

c.3The Type attribute of Store takes a value of either regional or local store.

c.4The ProductCode attribute of Product takes a character string value in therange 00000 to 99999. (a) Give the SQL required tocreate a database that represents the E–R data model for the retail chain givenin Figure 1.

In your database definition, you should use:

the‘posted foreign key’ approach to implement the WorksAt and Managesrelationships;

SQLdomains to implement the additional constraints.

(b) Give the SQLrequired to modify the database definition you gave as your solution to part(a) to include CHECK constraints thatrepresent the following additional constraints:

Additional constraints

c.5 The managerof a store (represented by an occurrence of the Manages relationship) works atthe same store (represented by an occurrence of the WorksAt relationship).

c.6 Regionalstores must stock all of the products.