CIS 420 Database Management Systems Post University
CIS420 – Database Management Systems Unit 3 Lab Due Date: 11:59 pm EST, Sunday of Unit 3 Points: 100 Overview: Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed to protect the data and make the database more flexible by eliminating redundancy and inconsistent dependency. Lab 3 will have you work with the normalization of database tables. Instructions: Given the sample records in the CHARTER table shown in Table 3.1, do the following: Table 3.1. Sample CHARTER Records Attribute Name CHAR_TRIP CHAR_DATE CHAR_CITY CHAR_MILES CUST_NUM CUST_LNAME CHAR_PAX CHAR_CARGO PILOT COPILOT FLT_ENGINEER LOAD_MASTER AC_NUMBER MODEL_CODE MODEL_SEATS MODEL_CHG_MILE Sample Value 10232 15-Jan2018 STL 580 784 Brown 5 235 lbs. Melton 1234Q PA31-350 10 $2.79 Sample Value 10233 15-Jan-2018 Sample Value 10234 16-Jan-2018 MIA 1,290 231 Hanson 12 18,940 lbs. Chen Henderson O’Shaski Benkasi 3456Y CV-580 38 $23.36 TYS 524 544 Bryana 2 348 lbs. Henderson Melton 1234Q PA31-350 10 $2.79 Sample Value 10235 17-Jan2018 ATL 768 784 Brown 5 155 lbs. Melton 2256W PA31-350 10 $2.79 Q1: Write the relational schema and draw the dependency diagram for the table structure. Make sure that you label all dependencies. CHAR_PAX indicates the number of passengers carried. The CHAR_MILES entry is based on round-trip miles, including pickup points. (Hint: Look at the data values to determine the nature of the relationships. © 2022 Post University, Waterbury, CT ALL RIGHTS RESERVED For example, note that employee Melton has flown two charter trips as the pilot and one trip as the copilot.) The dependency diagram is shown in Figure 3.2. Figure 3.2. The Dependency Diagram CHAR_TRIP CHAR_DATE CHAR_CITY CHAR_MILES CUST_NUM CUST_LNAME CHAR_PAX CHAR_CARGO Transitive dependency PILOT COPILOT FLT_ENGINEER LOAD_MASTER AC_NUMBER MOD_CODE MOD_SEATS MOD_CHG_MILE Transitive dependencies The relational schema is written as follows: CHARTER(CHAR_TRIP, CHAR_DATE, CHAR_CITY, CHAR_MILES, CUST_NUM, CUST_LNAME, CHAR_PAX, CHAR_CARGO, PILOT, COPILOT, FLT_ENGINEER, LOAD_MASTER, AC_NUMBER, MODEL_CODE, MODEL_SEATS, MODEL_CHG_MILE) Q2: Decompose the dependency diagram in Problem 11a to create table structures that are all in 3NF and write the relational schema. Make sure that you label all dependencies. The normalized dependency diagram is shown in Figure 3.3. (Note the addition of MOD_CODE in the AIRCRAFT table to serve as the AIRCRAFT table’s FK to MODEL.) © 2022 Post University, Waterbury, CT ALL RIGHTS RESERVED Figure 3.3. The Normalized Dependency Diagram CHARTER table CHAR_TRIP CHAR_DATE CHAR_CITY CHAR_PAX CHAR_MILES CUST_NUMBER PILOT Continued …. COPILOT FLT_ENGINEER LOAD_MASTER CUSTOMER table CUST_NUMBER CUST_LNAME AIRCRAFT table AC_NUM MOD_CODE MODEL table MOD_CODE MOD_SEATS MOD_CHG_MILE Q3: Draw the Crow’s Foot ERD to reflect the adequately decomposed dependency diagrams you created in Q2. Make sure that the ERD yields a database that can track all of the data shown. Show all entities, relationships, connectivities, optionalities, and cardinalities. The initial Crow’s Foot ERD is shown in Figure 3.4. © 2022 Post University, Waterbury, CT ALL RIGHTS RESERVED Figure 3.4. The Initial Crow’s Foot ERD While the ERD shown in Figure 3.4 faithfully reflects the results generated by the normalization process, it has a significant design flaw. This flaw has the following consequences: • • • If additional crewmembers such as copilots, loadmasters, and flight engineers are not assigned to the flight, the CHARTER table will include many nulls. (Much smaller aircraft used in charter flying require only a pilot and a functioning autopilot. The Federal Air Regulations (FARs) that govern charter aviation permit single-pilot operations for aircraft that have less than 12,500 lbs. gross take-off weight and that are not turbine-powered.) Including COPILOT, FLT_ENGINEER, and LOAD_MASTER also produce synonyms in the CHARTER table. As the aircraft used in the charter flights become larger and more complex, crews become more extensive, thus producing more synonyms and potential nulls. (Not to mention that the CHARTER table will have to be modified to accept additional crew members such as flight attendants.) The problems associated with the ERD shown in Figure 3.4 are eliminated through CREW’s composite entity in Figure 3.5. Note that this modification makes it possible to assign any number of crewmembers. A job attribute can be added to the EMPLOYEE entity to ensure that the crewmembers are appropriately qualified. The applications software can assign crewmembers based on job classifications such as pilot, loadmaster, flight attendant, etc. Because only some employees are qualified as crewmembers, CREW is optional to EMPLOYEE. But each crewmember must be an employee, so EMPLOYEE is mandatory to CREW. © 2022 Post University, Waterbury, CT ALL RIGHTS RESERVED Figure 3.5. The Final Crow’s Foot ERD Note that the application is shown in Figure 3.6 — based on the design shown in Figure 3.6 — enables the end-user to input only those crew members required for the charter flight. (In this case, only two crew members are needed, but the design permits adding many more crew members without making structural changes in the database tables. Such flexibility is the essence of good design.) © 2022 Post University, Waterbury, CT ALL RIGHTS RESERVED Figure 3.6. Sample Charter Record Requirements: • Submit a Word document. Be sure to read the criteria below by which your work will be evaluated before you write and again after you write. © 2022 Post University, Waterbury, CT ALL RIGHTS RESERVED Evaluation Rubric for Unit 3 Lab CRITERIA Accuracy Completeness Deficient 0 – 44 points 0-69% accurately answers the questions. 0 – 14 points Lab is not very complete. Answering a few questions. © 2022 Post University, Waterbury, CT ALL RIGHTS RESERVED Needs Improvement 45 – 59 points 60-79% accurately answers some questions. 15 – 19 points Lab is somewhat complete. Answering some (at least 70%) questions. Proficient Exemplary 60 – 74 points 80-99% accurately answers most questions. 75 points 100% accurate accurately answers all questions. 20 – 24 points Lab is mostly complete. Answering most (at least 90%) questions. 25 points Lab is complete. Answering all (100%) questions.
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
