Database Systems Assignment

Solution

Task Title: Database Systems Assignment 3

Subject Code: ITC556_201960

University: Charles Sturt University

Objective: Main objective of this assignment to get familiar with the Database designing aspects like Normalization, ER Diagram designing, Functional Dependency identification etc.

 

Overview: For this task, a sample dataset is given in the form of a table. From the given table, student suppose to derive the data entities. After the entity identification, students must perform Entity relationship diagram (ERD) generation, Dependency diagram drawing and relation normalization up to a third normal form (3NF).

 

Required Environment:

  • For the drawing of the database designing diagrams draw.io online designing tool is required.

Steps of the Assignment:

  • Designing the initial Dependency Diagram: This is entirely done based on the given table. Only one relation is derived from the entire table.
  • Partial dependency removal (2NF conversion): As there was no multivalve attribute, so the relation is directly converted into 2NF by removing the partial dependency.  
  • Transitive dependency removal (3NF-conversion): 2NF relation can be converted to the 3NF form by removing all transitive dependencies.
  • Representation of the normalized relationship using ERD: Entity Relationship Diagram is used to represent the entire normalized relationship. Crowfoot notation are used in the ERD.

 

Structure of the given sample data:

Attribute Name

Sample Value

Sample Value

Sample Value

Sample Value

EMP_NUM

123

104

118

 

PROF_RANK

Professor

Asst. Professor

Assoc. Professor

Assoc. Professor

EMP_NAME

Ghee

Rankin

Ortega

Smith

DEPT_CODE

CIS

CHEM

CIS

ENG

DEPT_NAME

Computer Info.

Systems

Chemistry

Computer Info.

Systems

English

PROF_OFFICE

KDD-567

BLF-119

KDD-562

PRT-345

ADVISEE

1215, 2312, 3233,

2218, 2098

3102, 2782, 3311, 

2008, 2876, 2222,

3745, 1783,

2378

2134, 2789, 3456,

2002, 2046, 2018,

2764

2873, 2765, 2238,

2901, 2308

COMMITTEE_CODE

PROMO, TRAF

APPL, DEV

DEV

SPR, TRAF

PROMO, SPR

DEV

JOURNAL_CODE

JMIS, QED,

JMGT

 

JCIS, JMGT

 

 

Figure 3: ERD of the Normalized Relationship

 

 

Assessment item 3 back to top Normalisation Value: 15% Due Date: 13 -Sep -2019 Return Date: 09 -Oct -2019 Submission method options: Alternative submission method Task back to top Suppose that you have been given the table structure and data shown in Table below, which was imported from an Excel spreadsheet. The data reflect that a professor can have multiple advisees, can serve on multiple committees, and can edit more than one journal. Table 1: Sample PROFESSOR Records Attribute Name Sample Value Sample Value Sample Value Sample Val ue EMP_NUM 123 104 118 PROF_RANK Professor Asst. Professor Assoc. Professor Assoc. Professor EMP_NAME Ghee Rankin Ortega Smith DEPT_CODE CIS CHEM CIS ENG DEPT_NAME Computer Info. Systems Chemistry Computer Info. Systems English PROF_OFFICE KDD -567 BLF -119 KDD -562 PRT -345 ADVISEE 1215, 2312, 3233, 2218, 2098 3102, 2782, 3311, 2008, 2876, 2222, 3745, 1783, 2134, 2789, 3456, 2002, 2046, 2018, 2764 2873, 2765, 2238, 2901, 2308 2378 COMMITTEE_CODE PROMO, TRAF APPL, DEV DEV SPR, TRAF PROMO, SPR DEV JOURNAL_CODE JMIS, QED, JMGT JCIS, JMGT Given the information in Table 1: a. Draw the dependency diagram. (3 marks) b. Identify the multivalued dependencies. (3 marks) c. Create the dependency diagrams to yield a set of table structures in 3NF. (6 marks) e. Draw the Crow’s Foot ERD to reflect the dependency diagrams you drew in Part c. ( Note: You might have to create additional attributes to define the proper PKs and FKs. Make sure that all of your attributes conform to the naming conventions.) (3 marks) NOTE For guidance on how to normalise database relations from a dependency diagram, watch this very short YouTube video: https://www.youtube.com/watch?v=vkUyuLLgmwQ Manually drawing the dependency diagram, or the set of 3NF relations or the relatio nal schema is NOT acceptable. Rationale back to top This assessment task will assess the following learning outcome/s: • be able to apply normalisation techniques to optimise a database design. Marking criteria and standards back to top The marking criteria for t his assignment is: Criteria HD DI be able to apply Analysis Analysis normalisation techniques to optimise a database design. identifies all PKs and functional, partial and transitive dependencies. Accurately identifies all 3NF relations, including PKs, relevant attributes and referential integrity constraints. Creates an ERD that represents all 3NF relations and includes all entities, PKs, attributes, relationships, associative entities (if applicable), cardinalities and optionalities. identifie s PKs and functional, partial and transitive dependencies with minor omissions. Accurately identifies 3NF relations, including PKs, relevant attributes and referential integrity constraints with minor omissions. Creates an ERD that represents identified 3NF relations and includes all entities, PKs, attributes, relationships, associative entities (if applicable), cardinalities and optionalities. Requirements back to top Either save your diagrams in a PDF file or embed them in a Word document. Don't use any other file formats Please include your student ID, name and subject code in your submission. Assessment item 4 back to top Query databases using SQL Value: 15% Du e Date: 04 -Oct -2019 Return Date: 28 -Oct -2019 Submission method options: Alternative submission method Task back to top Task: Complex SQL Quer ies back to top NOTES • This assignment requires you to use SQLite to complete the tasks listed below. • For guidance on how to install SQLite, watch this short YouTube video: https://www.youtube.com/watch?v=9ynCDVg54H0 • For guidance on how to create tables using SQLite, watch this short YouTube video: https://www.youtube.com/watch?v=kij5lPw0F -o • Typing the SQL statements or the results is NOT acceptable. Instructions From Resources under Main Menu in Interact 2 go to Assignment 4 Folder Copy sqlit e3.exe file from the Assignment 4 Folder into a folder in your machine Copy a4.txt from the Assignment 4 Folder into the folder in your machine where your SQLite file is located From the SQLite Command Prompt execute the a4.txt script. To do this, at t he SQLite Command Line type .read a4.txt [to create the tables you will use to answer this assignment’s questions] At the SQLite Command Line type .headers on At the SQLite Command Line type .mode column Write and run SQL statements to complete the f ollowing tasks (Each Query : 1.5 (0.75 query+ 0.75 for screenshot) Marks) 1. Show sums of line units for each invoice. 2. Show the details of the products that do not have a value for the attribute v_code. 3. Show the details of the invoices whose subtotal is great er than 24 but less than 76. 4. Show the details of the invoice who has the minimum subtotal. 5. Show the codes and names of the vendors who supplied products. 6. Using EXCEPT show the codes of the vendors who did not supply any products. 7. Using ‘NOT IN’ show the c odes and names of the vendors who did not supply any products. 8. List the names and codes of vendors and the number of products each vendor has supplied, i.e. vendor XXX has supplied xxx products, and vendor YYY has supplied yyy products etc. 9. Show the detail s of the employees who are located in area code 615. 10. Using inner join, list the details of the products whose line price is greater than 99. You are required to submit: 1. The SQL statements for each query, which should be copied from your SQLite3 Command Line and pasted into your submission file. If you type .echo on at the SQLite3 Command Line, SQLite will output your SQL statement with the results of the query making it easy to copy and paste both. 2. The screenshots of the results of the S QL statements which are to be copied from your SQLite3 Command Line and pasted into your submission file immediately after you execute the SQL query. If you used an output file please submit only the output file. Rationale back to top This assessment task will assess the following learning outcome/s: • be able to use complex SQL commands to query a database. Marking criteria and standards back to top The marking criteria for this assignment is: Criteria HD DI be able to use complex SQL commands to query a database. SQL syntax re turns all attributes that meet the business requirement from the tables that hold the data and applies the correct restrictions to retrieve the required information. SQL syntax returns all attributes that meet the business requirement from the tables that hold the data with minor omissions and applies appropriate restrictions to retrieve the required information. Presentation back to top The document must be prepared in a professional manner with clarity and sound organization. Requirements back to top Either submit your assignment as a PDF or a Word document. Don't use any other file formats. Please include your student ID, name and subject code in your submission.