Page | 1 BCIS 4660 Introduction to Data Warehousing Assignment 2 Objectives • Application of database design process o Conceptual, logical, and physical database design o Application of normalization • Apply E-R modeling Instructions 1. Create a new Word document. Do not use the current document. 2. Submit a Word document with your name on the cover page o To create a cover page, click Insert, Cover Page, and select a cover page) 3. Clearly identify your name and assignment name on the cover page 4. The Word document should contain the requested screenshots and answers to any questions 5. Each screenshot image must include a caption (e.g., Screenshot1: ERD1 Shipment) 6. Your screenshots should be legible 7. A professional quality work is expected. Points will be deducted for unprofessional work. Environment • VM BCIS 4660: SQL Server • ERDPlus https://erdplus.com/standalone Exercise: ERD & Database Design This assignment is designed to begin familiarizing you with database conceptual design using Entity Relationship Diagrams. 1. Observe the ER diagram for the GreenShop and answer the following questions about the diagram (5pts) Page | 2 a) How many tables will the relational schema mapped from the GreenShop ER diagram have? a. ________ b) What is the primary key column in the Product table? a. ________ c) How many columns will the following entities have in the relational schema mapped from the GreenShop ER diagram? a. Promotion ____ b. Product _____ c. Company _______ 2. In GreenShop, each employee reports to one department and a department must have at least one employee reporting to it. However, each department but could have many employees reporting to it. In the ER Diagram below, what symbols should represent the relationships (e.g., Zero or more, One and only one, One or more). Please justify the cardinality assignment. (5pts) a. Symbol 1 should be ___ b. Symbol 2 should be ___ Page | 3 3. In the ER Diagram for the BCIS4660 Course, what symbols should represent the relationships (e.g., Zero or more, One and only one, One or more). Please justify the cardinality assignment. (5pts) a. Symbol 1 should be ___ b. Symbol 2 should be ___ c. Symbol 3 should be___ d. Symbol 4 should be___ 4. In the ER Diagram for the Music, what symbols should represent the relationships (e.g., Zero or more, One and only one, One or more). Please justify the cardinality assignment. (5pts) c. Symbol 1 should be ___ d. Symbol 2 should be ___ e. Symbol 3 should be___ f. Symbol 4 should be___ 5. In this exercise, please put your learned concepts into action by converting the following scenario into an ERD. A manager wants to store information on their employees. The employees are identified by an Employee ID. In additional to storing the employees’ last name, first name, phone number, and zipcode, each employee is assigned to a department which is identified by the department ID. Each department has a budget amount, and department name. Please create an ERD that captures this information about this organization. Be certain to indicate identifiers and cardinality. Please justify the cardinality assignment. (Screenshot1) Grading (7pts). Make sure that in the ERD, the: • Entities are correctly identified: • Attributes are correctly identified: Page | 4 • Primary keys are correctly identified: • Relationships and cardinality are correctly identified: 6. Shipack is a packaging and shipping company with an enterprise database system that tracks its shipments. Shipped items are characterized by unique item number, weight, dimensions, destination address, and delivery date. Shipped items are received into Shipack’s system at a single retail center. Each retail center is characterized by its type, a unique retail identification number, and address. Shipped items arrive at their destination through one or more standard Shipack transportation events (i.e., flights, truck deliveries). These transportation events are characterized by a unique schedule identification number, and a transportation type (e.g., flight, truck). Create an Entity Relationship Diagram that captures this information about the Shipack system. Be certain to indicate identifiers and cardinality constraints. Please justify the cardinality assignment. (Screenshot2) Grading (7pts). Make sure that in the ERD, the: • Entities are correctly identified: • Attributes are correctly identified: • Primary keys are correctly identified: • Relationships and cardinality are correctly identified: 7. You have just started working as a Junior Data Analyst at GreenShop. Your manager hands you an Excel spreadsheet with the following columns (see table below) and asks you to design a database to store this data (16pts). a. Describe the steps to normalize this dataset (table) up to 2NF? b. After this dataset is normalized, how many entities should this dataset have? (Hint: start by identifying the different entities present in this dataset) c. Use ERDPlus to design the ERD for entities and attributes. (Screenshot3 - make sure to show the 4 required elements in your screenshot) d. Write and execute the T-SQL to create the tables. Please use elegant and rerunnable scripts Meaning, there should not be any errors in your script. (HINT: USE “databasename”; DROP “object type” IF EXISTS). (Screenshot4 - make sure to show the 4 required elements in your screenshot, including the expanded objects in the object explorer). e. Using the SQL Server database diagramming tool, create and view the E-R-Model of the physical implementation. i. Provide a short description of your actions ii. Provide a Screenshot5 showing E-R model. (See instructions) Page | 5 EnrollID StudentID FirstName LastName EnrollDate CourseID CourseName1 CourseID CourseName2 1011 91231 Ken Smith 2021/02/2021 1123 BCIS4660 1156 BCIS 4570 1012 89091 Ada Ogundele 2021/02/2021 1124 BCIS 4630 1123 BCIS4660 1013 23347 Sri Ram 2021/02/2021 1156 BCIS 4570 1123 BCIS4660 1014 84354 Julie Lin 2021/02/2021 1124 BCIS 4630 1123 BCIS4660 1015 34536 Barbara Lilipot 2021/02/2021 1124 BCIS 4630 1123 BCIS4660 1016 45456 John Gerry 2021/02/2021 1156 BCIS 4570 1123 BCIS4660 1017 33435 Mark Goode 2021/02/2021 1124 BCIS 4630 1123 BCIS4660 1018 35345 Red Darlion 2021/02/2021 1156 BCIS 4570 1123 BCIS4660 1019 35378 Janet Rino 2021/02/2021 1124 BCIS 4630 1123 BCIS4660 1020 98065 Porter Cruz 2021/02/2021 1156 BCIS 4570 1123 BCIS4660