Assignment 5 – Create a Relational Database from an EER Diagram
Assignment Goal: The goal of this assignment is to give you practice with mapping from a given enhanced entity relationship (EER) diagram into the relational model. As you know, EER diagrams do not capture all of the real world constraints that may exist so I have also provided a textual description of the constraints in the Builder’s R Us company. You have to implement the design in Oracle SQL Developer.
Instructions:
Part A) Using the EER and the scenario below create a relational database design. Your design should be depicted using the ascii format from the book (see excercises 5.14, 5.15, 5.16 and 5.17), where the table name is in UPPERCASE, primary keys are underlined, attributes are included as a comma separated list, and foreign keys are stated using fully qualified attribute names. For example a company database might include the following :
EMPLOYEE( EmployeeID , EmployeeFN , EmployeeLM , startDT , DepartmentID )
FK: EMPLOYEE.DepartmentID –> DEPARTMENT.DepartmentID
DEPARTMENT( DepartmentID , DepartmentNM , ManagerSSN , ManagerStartDT )
FK: DEPARTMENT.ManagerSSN –> EMPLOYEE.SSN
The sql script used in creating tables should be uploaded to Moodle. In addition to the primary keys, you should apply validation rules to ensure that the values entered are consistent with the domain of each attribute.
Part B) There are many ways to map a given superclass /subclass relationship. List each of the superclass /subclass relationships in the EER and the possible mappings available to you. Then describe your rational for why you chose option 8A, 8B, 8C or 8D.
Part C) Real world constraints should influence your database design decisions. The hard-constraints imposed by total and partial participation and by disjoint or overlapping membership often do not narrow your choice to just one of the 8A, 8B , 8C or 8D options. What other information should you consider to choice between the remaining choices? List two additional questions (other than participation and membership) that you could ask the CEO that would help you distinguish between options 8A, 8B, 8C or 8D. Specify the superclass /subclass relationships in the EER that their response would help you with and how their response might change your decision on how to map from the EER into the relational database design.
Submission Instructions:
Upload your solution into moodle. Please include your last name as the start of your file name include your name in the top right hand corner of each file. You can places parts A, B, and C the same file if you like. The file can be PDF, text or a word file.
Part A – a textual form of your relational model; SQL scripts used in implementing you database.
Part B – your decisions regarding superclass/subclass relationships
Part C – the two questions that you would ask to elicit information from a user
Optional – Any additional assumptions that you have made during the design process. Note that these assumptions must not conflict with either the EER diagram or the textual description.
Scenario: Builder’s R Us (BRU) is a company that helps customers design a variety of custom home design projects such as renovating a kitchen, painting the entire house, or installing a fence. The CEO wants to you to create a database so that she can keep track of expenses, income, customer information, employee performance, and inventory. She has already paid a consultant who has provided an enhanced-entity relationship diagram that reflects her business needs. You should enforce as many of the constraints outlined in the EER and the following textual description of BRU’s business practices as the basis for your relational database design.
A project can take place either inside or outside of a customer’s home. If the project is inside, the database should capture each room that will be affected by the project ( eg kitchen, living room, bathroom ). If the scope of the project is outside then database should capture each of the outside locations ( eg front yard, backyard, near the bbq ). The EER diagram includes generic term “location” to capture either rooms or outside locations. Of course, a customer may want a project that may require changes to both the inside and the outside of their home. Projects requiring both inside and outside work would have multiple records within the database – one to capture the overall project, one that captures all the work to be done inside, and a third project to capture all the work outside. The partOf relationship which is connected to the PROJECT entity reflects this requirement.
Builder’s R Us serves two types of customers – commercial and residential. Commercial customers receive a discount on all projects. Builder’s are Us send out a birthday card to their residential customers each year, so they need to capture the customer’s birth date. The company can’t currently afford to include advertising information in their database, but they plan to include that information at some point in the future. In the meantime, she would like to keep track of the market sector for each residential customer. In the past, employees have been less than diligent in entering the market sector, so she would be very happy if the database helped to correct the missing data.
To ensure good service, BRU assigns one employee to be a customer’s primary contact. The primary contact follows the project from conception through completion and may initiate new projects with the customer. If an employee leaves the company, the company assigns their customers to a new employee. Thus, at any given point in time, a customer has only one primary contact, but the database will keep historical information so that the owners can keep track of who is generating the most business. It happens infrequently, but an employee can be re-assigned to customer that they have previously worked with. Employees may also leave the company and then return at a later date . In such cases the database should provide them with the same identifier.
The majority of the employees in the company are trades-people such as painters, plumbers, and architects. One of the assurances that Builder’s R Us provide their customers is that all employees working on a project have trade certificates. BRU send out a courtesy reminder if a tradesperson’s certificate is about to expire, so the database should track the last date that the employee was certified. The database must also keep historical certification information so that if the company is ever audited the owner can show that ach tradesperson has been continually certified. Some of the company’s employees are particularly talented and have multiple certificates for a range of skills.
Costs are estimated on a job by job basis. Each job includes the tradesperson who performed, the project for which the work was conducted and the skill employed for the job. After the job is complete the actual cost is recorded. Trades people are required to submit a separate receipt for each service, even if the service is for the same project.
Occasionally materials provided for a project are faulty. Although the customer or employee usually returns the faulty material, Builder’s are Us will accept returns from anyone. The company does however keep contact information for the person returning the material so that they can run reports on frequent returns. Of course, the database should allow the same person to return the same material. Also important is the reason why the material was returned so that the company can change suppliers if required. Either the person making the return or a BRU employee will enter the project for the returned material.
Upload each section to moodle, make sure your files have your name and student id number in the top right hand corner of each page wherever possible. Include any additional assumptions that you have made during the design process. Note that these assumptions must not conflict with either the EER diagram or the textual description.
The EER diagram is available on moodle
Grading Criterion:
Part A-1) Traditional Schema (35)
– Relations and attributes (20)
– Primary and Foreign keys (15)
Part A-2) Data Implementation (25)
– SQL statements on creating tables, primary and foreign keys, data types, required fields, and constraints
– Don't forget to grant the instructors access to your tables
Part B) Options and rational 5 points each
Part C) Questions 5 points each
