JustAnswer



Assignment

Normalization

Page 1 of 3

The purpose of this assignment is to gain additional experience in converting relations into third normal form.

WHAT YOU WILL NEED FOR THIS ASSIGNMENT

• Make any reasonable assumptions for each of the two (2) problems described below.

• Treat each problem separately.

• Do not assume the problem away! (i.e., do not add additional fields to these problems or use any more data than is present).

• Do not add a unique primary key, such as a customer number, to eliminate a composite primary key.

• Look at all of the sample data given in the table as being representative of that which would be in the database; don't analyze it too much or make too many assumptions.

• Identify functional dependencies: primary key(s), foreign key(s), partial functional dependencies and transitive dependencies.

• Name each relation and show it in 1NF, 2NF and 3NF in dependency diagram format and also in equation format [see figure 6.3 in the text for an example of a 1NF dependency diagram]. See figures 6.4 and 6.5 in text for examples of 2NF and 3NF diagrams.

• Underline all PKs and dot-underline or italicize all FKs in all 1NF, 2NF and 3NF diagrams and equations.

• Draw your dependency diagrams in Visio (click HERE for a short video on how to do this), MS Word, PPT, Excel, etc.

• Import your final drawing into your MS Word report document called Assignment5-1.docx prior to submitting it to the instructor for grading.

ACTION ITEMS

Part 1: Consider the following daily orders report from A2Z Community Hospital (0-20 points)

| | | | | | |

|Kim Jones |Worthington | |Mowing | |2| |Carlos |

| | | | | |5| | |

| | | | | |.| | |

| | | | | |0| | |

| | | | | |0| | |

|John Smith |Dublin | |Mowing | |7| |Fred |

| | | | | |5| | |

| | | | | |.| | |

| | | | | |0| | |

| | | | | |0| | |

| | | |Aeration | |120.00 | |

| | | |Fertilizing | |

|Barbara Wentz |Worthington | |Mowing | |5| |Carlos |

| | | | | |0| | |

| | | | | |.| | |

| | | | | |0| | |

| | | | | |0| | |

|Charles Lee |H| |Mowing | |60.00 | |

| |i| | | | | |

| |l| | | | | |

| |l| | | | | |

| |i| | | | | |

| |a| | | | | |

| |r| | | | | |

| |d| | | | | |

|Heinz Schmidt |Powell | |Aeration | |255.00 | |

|Carrie Fisher |Columbus | |Fertilizing | |4| |Gina |

| | | | | |5| | |

| | | | | |.| | |

| | | | | |0| | |

| | | | | |0| | |

James Count |Dublin | |Mowing | |60.00 | |Sam |L21 | | | | | | | | | | | | | | | | | | | | | |

Assumptions: The task report is for a particular day or week. Client Name is okay as multi-valued attribute for this problem.

1. Fill repeating groups by recreating the table/relation above in your MS Word report file called Assignment5-1.docx and filling in all empty cells. (0-4 pts)

2. Put relation in 1NF dependency diagram and equation format (see Figure 6.3 in text), identifying the full functional dependency, all PKs (underlined), partial functional dependencies (labeled), and transitive dependencies (labeled). (0-4 pts)

3. Covert 1NF to 2NF by removing partial functional dependencies. Show in 2NF dependency diagram and equation format (see figure 6.4 in the text). (0-4 pts)

4. Convert 2NF to 3NF by removing transitive dependencies. Show in 3NF dependency diagram and equation format (see figure 6.5 in the text). (0-4 pts)

Page 3 of 3

5. Draw either a Crow’s Foot or Chen ERD for the 3NF equations created, including all PKs,

FKs, attributes, connectivities, and cardinalities. ERD should match 3NF diagram. (0-4 pts)

WHAT TO SUBMIT

Submit a single MS Word document called Assignment5-1.docx with answers properly numbered for each part 1 and 2 to the drop box by the stated deadline.

SQL Lab : SQL Queries I

ACTION ITEMS

Background / Initial Setup

Consider the following set of tables in the MS Access relational schema below. The schema shows 4 tables. Primary Keys (PKs) are underlined. All tables have a 1:M relationship with the table it connects to (Foreign Keys (FKs) are at the many (or ∞) end of the lines connecting tables). Notice that a field can be both a PK and a FK.

Assignment

SQL Lab : SQL Queries

Page 2 of 5

Below are the tables you will be creating for this assignment (in equation format):

COURSE (CRS_CODE, CRS_DESCRIPTION, CRS_CREDIT)

CLASS (CLASS_CODE, CRS_CODE, CLASS_SECTION, CLASS_TIME, CLASS_ROOM)

ENROLL (CLASS_CODE, STU_NUM, ENROLL_GRADE)

STUDENT (STU_NUM, STU_LNAME, STU_FNAME, STU_INIT, STU_DOB, STU_HRS, STU_CLASS, STU_GPA, STU_PHONE)

NOTE: ALL PKs are underlined. All FKs are italicized. Some fields are both underlined and italicized!

The following image shows the data present in each of the above tables:

Assignment

SQL Lab : SQL Queries I

Page 3 of 5

Below are the data types for the attributes listed in the tables above:

All attributes say exactly that need to use the char data type and all attributes say up to that need to use the varchar data type. None of the attribute values can be null unless stated can be NULL below. Only use the integer data type for all integer values stated as such. Use number(x,y) where indicated.

CRS_CODE – up to 8 characters, PK in COURSE, FK in CLASS

CRS_DESCRIPTION – up to 35 characters

CRS_CREDIT – an integer in range of (1, 2, 3, 4), default 4

CLASS_CODE – a 5-digit number, PK in CLASS, PK and FK in ENROLL

CLASS_SECTION – an integer in range of (0, 1, 2, 3, 4, 5, 6, 7, 8)

CLASS_TIME - up to 25 characters

CLASS_ROOM - exactly 6 characters (can be NULL)

STU_NUM – an integer, PK in STUDENT, PK and FK in ENROLL

STU_LNAME - up to 25 characters

STU_FNAME - up to 20 characters

STU_INIT - exactly one character (can be NULL)

STU_DOB - date

STU_HRS – an integer (greater than or equal to 0 and less or equal to than 1000), default 0

STU_CLASS - exactly 2 characters in range of (Fr, So, Jr, Sr, Gr)

STU_GPA - a number with 2 decimal places from 0.00 to 4.00, default 0.00

STU_PHONE - a 4-digit number

ENROLL_GRADE - exactly 1 character in range of (A, B, C, D, F, I, W, Z), default Z

Assignment

SQL Lab : SQL Queries

Page 4 of 5

Part 1: Creating the database tables (0-15 points)

Open Notepad to create and save a script file called lab2part1.sql that contains the complete set of SQL DDL commands needed to drop and create the above tables (the data inside these tables will be inserted in part 2 below). Follow the example given in the Meet session.

Use comments to explain your code where appropriate. Use primary key constraints, foreign key constraints and other types of constraints (such as NOT NULL, CHECK, etc.) as dictated in the background above. Additional samples on how to format these are given in the links above.

Notes

Include set echo on; and set serveroutput on; commands at the beginning of file.

Drop all tables before first create with cascade constraints set.

Include all defaults, constraints, foreign keys, check statements, etc. in one create table command for each table (i.e., no alter table commands for this lab).

Create the tables in the proper order (e.g., if Table Y has a foreign key constraint referencing table X, then table X must be created BEFORE table Y because table Y can’t reference table X if table X doesn’t exist yet).

Do a commit; at the end of your file.

Do not use on delete cascade for any foreign key declarations in this lab.

Open and run the lab2part1.sql script file in SQL Developer in the virtual desktop.

Fix any errors that appear in the script output area directly in the worksheet area of SQL Developer (save to the file you opened).

Clear the output area and rerun the script file a second time.

Continue steps 3 to 4 until your script runs without any errors.

When the tables are successfully created and there are no errors in the script output area, press on the save button in the script output area and save the output to a file called lab2part1.lst (be sure to clear this area using the eraser in the output area with each new run or all previous output will concatenate into the .lst file).

Part 2: Inserting data into the database tables (0-10 points)

Open Notepad to create and save a script file called lab2part2.sql that contains the complete set of SQL DDL commands needed to first delete data from and then insert data into the above tables according to the data given in the Background portion of this lab above (the deletes will allow you to rerun this script multiple times). Follow the example given in the Meet session.

You should be careful to insert data into the tables in proper order (a record using a FK from another table can’t reference that FK if it doesn’t exist yet).

Notes

Assignment

SQL Lab : SQL Queries

Page 5 of 5

Include set echo on; and set serveroutput on; commands at the beginning of file.

You must do the delete from commands in proper order; removing data from tables with foreign key constraints first, then those with no foreign key constraints last. This must be done to remove all table contents prior to the inserts in order to reuse the script.

Do a commit; at the end of your file.

Open and run the lab2part2.sql script file in SQL Developer in the virtual desktop.

Fix any errors that appear in the script output area directly in the worksheet area of SQL Developer (save to the file you opened).

Clear the output area and rerun the script file a second time.

Continue steps 3 to 4 until your script runs without any errors.

When the tables are successfully populated with data and there are no errors in the script output area, press on the save button in the script output area and save the output to a file called lab2part2.lst (be sure to clear this area using the eraser in the output area with each new run or all previous output will concatenate into the .lst file).

Part 3: Querying the database (0-10 points)

Execute SQL SELECT statements to accomplish each of the following. Run the commands separately in the worksheet area of SQL Developer (do not create a .sql file for these).

Include set echo on; and set serveroutput on; at the beginning of the worksheet area before each command. Copy the output from the script output area after each command executes and paste it into a single MS Word report file called lab2.docx. Clear the script output area prior to saving each command’s output.

o Display all records in the COURSE table o Display all records in the CLASS table

o Display all records in the STUDENT table o Display all records in the ENROLL table

o Display the all attributes of the record of the student whose STU_NUM is ‘324257’ o Display the STU_NUM only of students who have a GPA greater than 2.12

o Display the grades only of the student whose STU_NUM is ‘321452’

WHAT TO SUBMIT

Turn in 5 files b zipping them into one zip file for submission: the lab2part1.sql script file and lab2part1.lst output file from part 1, the lab2part2.sql script file and lab2part2.lst file from part 2, and the MS Word report file called Lab2.docx containing the queries and their output from part 3 (called lab2.docx). Zip all files into a single submission and turn your zipped file into the course drop box by the stated deadline.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download