"Use the following database for Problems 1, 2 and 3. This database holds information about employees of a small digital electronics company called Digital425:
• The database keeps track of all Digital425 employees. Each employee is identified by social security number. The name and address of each employee is also recorded.
• While there are support personnel, the principal employees of Digital425 are designers: hardware designers, software designers, and graphic designers. Additional informa- tion to document competence is required for designers: Hardware designers must have a license number. Software designers must have a Bachelors or Masters degree in computer science from an accredited school; the school and degree are recorded. Graphic designers must have had their portfolios certified by the head of the graphics department; the date of certification is recorded. Despite the fact that designers may be multi-talented, Digital425 will only allow each designer to be designated as one specific type: hardware, software or graphic.
• Each product is developed with a team led by one hardware designer, one software designer and one graphic designer. Because team leaders must be carefully matched, the company has put together in advance "leadership groups", which are triples of team leaders (one hardware designer, one software designer and one graphic designer). Each leadership group is recorded with an identifying number. To avoid over-work, any one designer can be assigned to no more than 3 leadership groups.
• Digital425 also uses outside service providers (companies providing services to Digi- tal425). These are also recorded in the database. Each provider is identified by the company name; the address is also recorded. Each provider must provide one or more liaisons to Digital425. For each liaison from a provider company, Digital425 must have the liaison's name and the liaison's telephone number at the provider company.
• Specific Digital425 employees are assigned to interact with specific service provider liaisons. These assignments are recorded in the database.
• Some graphics designers are very particular about the service providers who do their packaging. Therefore, each graphic designer may specify one specific service provider to do all his or her packaging. This request is recorded in the database with the date of the request.
Problem 1
Identify all the constraints on data or on the relationships between data in the database.
Problem 2
Draw an entity-relationship (ER) diagram describing the database. Represent the con-straints on data and the constraints on relationships between data. Are there any con-straints you cannot represent? If so, why? Your ER diagram will be judged primarily
on whether it correctly captures the database through entities and relationships. In addi-tion, the grading will consider whether it captures the database in a way that minimizes redundancy in the representation.
Problem 3
Part a Translate your ER diagram from Problem 2 into a relational schema.Describe the relational schema using SQL statements to create the relations. Also indicate any primary key, candidate key and foreign key constraints.
Part b Are there constraints that you cannot specify using only domain con-straints and primary key, candidate key and foreign key constraints? If so, for each such constraint, if possible, express it using SQL CHECKs or ASSERTIONs; if not possible, say
why. (Be sure to say in which table a CHECK appears.)