transform data model to physical model (database design)

Given that:

· One owner can own many properties, and OWNER data may be entered into the database before any associated property is identified or data for that property is entered.

· One property can have many services performed at it; and OWNED_PROPERTY data may be entered into the database before any service is performed at that property.

· One employee can work on many services, and EMPLOYEE data may be entered into the database before the employee has worked on any service.

· A GG service can be assigned to many Property services, and a GG service may be entered into the database before assigned.

Assignment Details:

1. Transform the data model to physical model (database design). a. Draw the physical entity-relationship diagram (ERD) in Crow’s foot notation using MS Visio

2013/2016. Use only the crow’s foot database notation (template) in MS Visio (10 pts. penalty is assessed if the wrong template is used). Remember what we discussed in class about depicting a physical model table (see figures 5-3, 5-5, 5-8, 5-9, and beyond for guidance). NOTE: Mac users who have difficulty running MS Visio on their Apple Mac’s can use Lucid Charts to develop the physical model then paste the charts in a MS Word file).

b. There must be no many-to-many relationships. c. The physical model must include not only tables, foreign keys and relationships with cardinalities but

also all columns and their data types (do not use char type only, you need to choose the appropriate data types for each column instead) and also display data types on the diagram (10 pts. reduction if data types are not included or displayed).

d. The names must be the same as provided in the data model; clearly label the primary keys and foreign keys.

e. All foreign keys are required, i.e., cannot be NULL. 2. Transform the physical model/database design to an actual database using MS Access 2013/2016 for all tables and relationships, using SQL statements (DO NOT use the Query-By-Example (QBE) feature available on MS Access). The data types for each column in Access should be compatible with those defined in database design above. Populate the tables with real data (you can make up) and each table needs to have at least 5 rows.

a. Please enforce referential integrity constraints when necessary and ensure the values satisfy the

constraints. Submission Guidelines: 1. This is an individual assignment. 2. Draw the physical model ERD in a MS Visio file and name it based on this notation: FirstnameLastnameHW3.vsdx (Note: Do not user a space in the file name). For Mac users, please get a screen shot of your model and past it in a MS Word submission. 3. Name the MS Access database file following this notation: FirstnameLastnameHW3.accdb (Note: Do not user a space in the file name). 4. Submit only the electronic copy of both Visio (MS Word for Lucid Charts user) and Access files on Blackboard

a. Upload and submit the two files on Blackboard before. b. You are strongly advised to submit the electronic copy at least 30 minutes before the deadline to avoid any technical issues. c. Make sure you submit the correct file and that the submitted files can be opened; the grading is based on your submitted files only. Caution: submit the actual Access database file that ends with .accdb, not the .laccdb file. You are strongly advised to verify the integrity of your submission by opening the files yourself after the submission. You could receive 0 point if wrong files are submitted or the submitted files cannot be opened by the campus computers. d. Late submissions are subject to the penalties outlined in the syllabus. Absolutely no exceptions! Submission system remains open till 48 hours after the deadline, and you will not be able to submit afterwards.