For This or a Similar Paper Click To Order Now

Spotify
Learning Goal: I'm working on a mysql question and need an explanation and answer to help me learn. 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 Promotion 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 (5pts). 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. In two sentences, please describe the relationships in the image below. Make sure to describe the two directions in the image. (5pts). 7. In two sentences, please describe the relationships in the image below. Make sure to describe the two directions in the image. (5pts). 8. 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 (10pts). 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. Page | 5 i. Provide a short description of your actions ii. Provide a Screenshot5 showing E-R model. (See instructions) EnrollID StudentID FirstName LastName EnrollDate CourseID CourseName1 CourseID CourseName2 1011 91231 Jay Smith 2022/02/20 1123 BCIS4660 1156 BCIS 4570 1012 89091 Leslie Ogundele 2022/02/20 1124 BCIS 4630 1123 BCIS4660 1013 23347 Izee Ram 2022/02/20 1156 BCIS 4570 1123 BCIS4660 1014 84354 Lola Lin 2022/02/20 1124 BCIS 4630 1123 BCIS4660 1015 34536 Pau Lilipot 2022/02/20 1124 BCIS 4630 1123 BCIS4660 1016 45456 John Carrie 2022/02/20 1156 BCIS 4570 1123 BCIS4660 1017 33435 Mark Luad 2022/02/20 1124 BCIS 4630 1123 BCIS4660 1018 35345 Red Darlion 2022/02/20 1156 BCIS 4570 1123 BCIS4660 1019 35378 Sri Rino 2022/02/20 1124 BCIS 4630 1123 BCIS4660 1020 98065 kalyani Cruz 2022/02/20 1156 BCIS 4570 1123 BCIS4660 9. Database reverse engineering: (5pts) a. Pick one of the following websites and perform reverse engineering to identify its major entities and their attributes (4 or more entities). List the entities and their attributes. i. Entity1 (attrib1, attrib2, attrib3...) ii. Entity2 (attrib1, attrib2, attrib3...) iii. Entity3 (attrib1, attrib2, attrib3...) iv. Entity4 (attrib1, attrib2, attrib3...) v. ...... b. Use ERDPlus to draw an ERD that represents the entities and attributes, as well as the relationships between the entities. (Screenshot) c. Describe the entities and attributes and what you accomplished. American Airlines (aa.com) Travelocity Twitter LinkedIm Canva Tiktok Match.com Canvas Spotify

For This or a Similar Paper Click To Order Now

Leave a Reply

Your email address will not be published. Required fields are marked *

For This or a Similar Paper Click To Order Now