Intro to Database Management

Project 3 Start Assignment Due Thursday by 11:59pm Points 100 Submitting a file upload File Types vsdx, pdf, xlsx, and docx Attempts 0 Allowed Attempts 3 Available Dec 6 at 12am - Dec 8 at 11:59pm Requirements A laboratory has several chemists who work on one or more projects. Chemists may use certain kinds of equipment on each project. Each chemist is identified by a 4-digit unique identifier; attributes of a chemist include name up to 25 characters long and optional phone number. A project is identified by a 3-digit unique identifier, attributes of a project include start date and end date. An equipment is identified by a 10-digit serial number, attributes of an equipment include cost and purchased date. The laboratory needs to record the date a given equipment is assigned to a particular chemist working on a specified project. A chemist must be assigned to at least one project and one equipment item. A given equipment item need not be assigned, and a given project need not be assigned either a chemist or an equipment item. Data . Chemists Luke Danshin (ID: 1001, Phone: 2223334444) o Moises Mandifield (ID: 1002) . Chase Rate (ID: 1003, Phone: 3334445555) Vicky Sawney (ID: 1004, Phone: 4442221111) . Clevey Ownsworth (ID: 1005) Projects 101 starting 2/3/2019 ending 5/23/2020 102 starting 7/25/2019 103 starting 9/12/2019 . Equipment Serial No. 1305483841 purchased at $25,000 on 2/21/2019 Serial No. 4020245712 purchased at $40,000 on 3/15/2019 Serial No. 5020384026 purchased at $122,000 on 3/27/2019 Serial No. 6043020583 purchased at $70,000 on 3/20/2019 . Serial No. 6070920302 purchased at $ 250,500 on 5/4/2019 Assignments Chemist 1001 and equipment 1305483841 for project 101 on 2/3/2019 Chemist 1002 and equipment 5020384026 for project 101 on 2/3/2019 Chemist 1003 and equipment 4020245712 for project 101 on 3/5/2019 Chemist 1001 and equipment 4020245712 for project 102 on 7/26/2019 Chemist 1004 and equipment 6043020583 for project 102 on 7/27/2019 Task 1: Conceptual Data Modeling Develop an Entity-Relationship Diagram (ERD) or Enhanced Entity-Relationship Diagram (EERD) based on the given requirements. The requirements should determine whether an ERD or EERD should be developed. Task 2: Logical Data Modeling Develop a Relational Data Model (RDM) in both graphical and textual formats. Ensure that RDM clearly identifies 1) primary and foreign keys together with referential integrity constraints, 2) functional dependencies. and 3) normal form designations. Task 3: Physical Database Design Develop metadata for the relational database. Refer to the metadata used in class exercises. Determine appropriate data type. size, and maximum/minimum values, if any, for each attribute based on the information provided in the requirements. For SQL data type, use NUMERIC data type for numbers, VARCHAR for alphanumeric values, and DATE for dates/timestamps. Task 4: Data Definition Develop a Relational Database in Microsoft Access using Structured Query Language (SQL). Table definitions must contain appropriate constraints for primary and foreign keys. Populate the database with the given set of data. All SQL statements must follow appropriate guidelines. Task 5: Data RetrievalDevelop SQL queries to retrieve the following information: 1. IDs and names of chemists assigned to project ID 101 together with the date of assignment 2. IDs of projects (including those that do not have any chemist assigned yet) and the number of chemists assigned to each project 3. IDs of projects (only those that have equipment assigned) and the total value of equipment assigned to each project Task 6: Privacy, Security, and Ethical Considerations Identify and describe one specific scenario for each of the following issues. The description of scenarios should be sufficiently detailed to explain why it is a relevant scenario for the specific issue. These scenarios may not necessarily be present in the solution you have developed for the above tasks. Refer to Privacy, Security, and Ethical Considerations in Databases lecture notes 1. Privacy issues that can potentially arise from the types of data stored and the way data is organized in the database. 2. Security issues that can affect the protection of data from unauthorized access and/or misuse. 3. Ethical issues that can impact the ethical and proper use and storage of data for all stakeholders. Submission Your submission must include the following files: . one Microsoft Visio file containing ERD/EERD and RDM in separate pages [Tasks #1 and #2]. one PDF file with printouts of both ERD/EERD and RDM [Tasks #1 and #2]. one Microsoft Excel file containing the metadata [Task #3], one Microsoft Access file containing the relational database populated with given data and SQL queries [Tasks #4 and #5], one Microsoft Word file containing descriptions of privacy. security, and ethical scenarios concerning the database [Task #0]. The submission files should be named CAP3786-84078 (Fall 2022) Project 3 - Full Name (replace Full Name with your full name; for example, CAP3786-84078 (Fall 2022) Project 3 - John Doe). Any submission that does not follow the instructions will result in point deductions. You can make reasonable assumptions if the requirements do not specifically provide necessary information to complete the diagrams. Create a note with all your assumption clearly and sufficiently detailed and attach it to the diagram. You are NOT required to make assumptions if you can find all necessary information to complete the diagrams. You must NOT make assumptions that either conflict with or override the requirements specified. Grading Your submission will be graded based on the following criteria: Are all requirements appropriately captured in the ERD or EERD using correct notations and syntax? . Are all parts of ERD or EERD correctly and completely captured in the RDM using correct notations and syntax? . Is the metadata correctly defined? . Are all components of RDM implemented in the relational database using correct SQL commands? Are all required information correctly retrieved using appropriate SQL queries? . Is a scenario for potential privacy issue in the database identified and sufficiently explained? . Is a scenario for potential security issue in the database identified and sufficiently explained? . Is a scenario for potential ethical issue in the database identified and sufficiently explained? CAP3786 Fall 2022 Rubric for Project 3 Criteria Ratings Pts Conceptual 15 pts 14 pts 12 pts 9 pts 6 pts pts 0 pts Data Student Student Student Student Student captures Student Student Modeling captures all captures most captures major captures some only a few captures only a does not requirements in requirements in requirements in requirements in requirements in few complete ERD or EERD ERD or EERD ERD or EERD ERD or EERD ERD or EERD requirements in ERD or 15 pts using using using using using appropriate ERD or EERD EERD. appropriate appropriate appropriate appropriate syntax. mostly syntax. syntax. syntax. syntax. incorrectly. Logical 15 pts 14 pts 12 pts 9 pt 6 pts 3 pts 0 pts Data Student Student Student Student Student captures Student Student Modeling captures all captures most captures major captures some only a few parts captures only a does not parts of ERD or parts of ERD or parts of ERD or parts of ERD or of ERD or EERD few parts of complete EERD in RDM EERD in RDM EERD in RDM EERD in RDM in RDM using ERD or EERD in RDM. 15 pts using using using using appropriate RDM mostly appropriate appropriate appropriate appropriate syntax incorrectly. syntax. syntax. syntax. syntax.Physical 12 pts 11 pts 10 pts 7 pts 5 pt 2 pts 0 pts Database Student Student Student Student Student Student defines Student Design correctly correctly correctly correctly correctly defines only a few parts of does not defines some define the 12 pts defines all defines most defines major only a few parts the metadata metadata. parts of the parts of the parts of the parts of the of the metadata. mostly incorrectly. metadata. metadata metadata. metadata. Data 15 pts 14 pts 12 pts 9 pts 6 pts 3 pt 0 pts Definition Student creates Student creates Student creates Student creates Student creates Student Student all parts of most parts of major parts of some parts of only a few parts creates only a does not relational relationa relationa relational of relational few parts of create database using database using database using database using relational relational 15 pts database using correct SQL correct SQL correct SQL correct SQL correct SQL database database. statements. statements. statements. statements. statements. mostly incorrectly- Data 15 pts 14 pts 12 pts 9 pts 6 pts 3 pt 0 pts Retrieval Student retrieves Student Student Student Student retrieves Student Student all correct data retrieves most retrieves most retrieves some only a few data retrieves only does not from database data from data from data from from database a few data retrieve 15 pts using correct database using database with database using using correct from database data from SQL statements. correct SQL some minor correct SQL SQL statements. mostly database. statements. errors. statements. incorrectly. Privacy 6 pts 4 pts 2 pts 0 pts Issue Student correctly identifies a Student correctly identifies a Student incorrectly identifies a Student does not potential privacy issue and potential privacy issue and potential privacy issue but identify a 6 pts provides an exceptional provides appropriate provides some justification. potential privacy justification. issue. justification Security 6 pts 4 pts 2 pts 0 pts Issue Student correctly identifies a Student correctly identifies a Student incorrectly identifies a Student does not potential security issue and potential security issue and potential security issue but identify a 6 pts provides an exceptional provides appropriate provides some justification. potential security justification. justification. issue. Ethical 0 pts 6 pts 4 pts 2 pts Issue Student correctly identifies a Student correctly identifies a Student incorrectly identifies a Student does not potential ethical issue and potential ethical issue and potential ethical issue but identify a 6 pts provides an exceptional provides appropriate provides some justification. potential ethical justification. justification issue. Submission 10 pts 8 pts 4 pts 0 pts Student submits the Student submits the project with Student submits the project with Student does not submit 10 pts project correctly minor issues. major issues. the project Total Points: 100