Design a relational database schema for the data provided

Assignment Help Database Management System

Reference no: EM132308872

Theory Assignment

Aims

Revise and apply the concepts relating functional Dependencies. Normalise a database schema to a desired normal form.

Construct a 3NF database schema, based upon a universal relation and a set of functional dependencies. Optimise a query using a query-tree representation.

Questions Set

Question 1

a) Discuss insertion, deletion and modification anomalies and illustrate with simple examples why they are bad.

b) Why are normal forms alone not sufficient as a condition to ensure good database design?

Consider the following database for a system that keeps track of suppliers (S), parts(P) and projects(J) with sample values supplied:

The table (SPJ) represents shipments of parts from suppliers to individual projects. (The S#, P# and J# fields are foreign keys that reference the S, P and J tables respectively.

Construct a relational algebra expression using symbolic notation for the following queries on this database. A Microsoft Word Document containing all of the relational algebra symbols is available here.

c) Get the names of projects that have at least one red part supplied to them.

d) The average quantity of each part supplied by each supplier to projects in London.

Question 2

The Scenario:

Suppose we have the following data set that contains information about students, the units they are studying and the degrees that they are enrolled in:

student_id	first_name	last_name	date_of_birth	degree	school	unit_code	unit_name	degree_length
55643	John	Smith	23/01/84	Bachelor of Science	Science and Technology	SCI100	Science in Practice	3
55643	John	Smith	23/01/84	Bachelor of Science	Science and Technology	COSC110	Introduction to Programming and the UNIX Environment	3
55643	John	Smith	23/01/84	Bachelor of Science	Humanities	PHIL102	The Art of Good Thinking	3
76423	Jane	Doe	4/06/91	Bachelor of Science	Science and Technology	COSC100	Introduction to Informatics	3
76423	Jane	Doe	4/06/91	Bachelor of Arts	Humanities	PHIL102	The Art of Good Thinking	3
76423	Jane	Doe	4/06/91	Bachelor of Arts	Behavioural, Cognitive and Social Sciences	GEPL111	Earth in Crisis?	3
96744	Bob	Smith	7/05/67	Bachelor of Arts	Humanities	PHIL102	The Art of Good Thinking	3
96744	Bob	Smith	7/05/67	Bachelor of Arts	Humanities	CLLA101	Introduction to Classical Languages	3
22342	Alex	Wise	24/09/77	Bachelor of Computer Science	Science and Technology	COSC110	Introduction to Programming and the UNIX Environment	3
22342	Alex	Wise	24/09/77	Bachelor of Computer Science	Science and Technology	COSC100	Introduction to Informatics	3
22342	Alex	Wise	24/09/77	Bachelor of Computer Science	Science and Technology	COSC210	Database Management Systems	3
44398	David	Jackson	10/07/91	Bachelor of Rural Science	Science and Technology	SCI100	Science in Practice	4
44398	David	Jackson	10/07/91	Bachelor of Rural Science	Science and Technology	CHEM110	Chemistry 1	4

44398

David

Jackson

10/07/91

Bachelor of Rural Science

Environmental and Rural Science

ECOL100

Ecology: Concepts and Applications

Here is a comma-separated-values (csv) file of the data presented above for you to review. This can be viewed with spreadsheet or text editing software and it may be easier to analyse the data using the sort and filter functions.

Task:

Your task is to design a relational database schema for the data provided. This database schema will need to be in 3NF. To do this you should first list all the functional dependencies that are implied from data, specify the key attributes and use these to normalise the schema so that it is in 3NF.

Once you have completed your relational schema, you should explain why each of the relations is in 3NF.

Question 3

Consider a database schema with attributes A, B, C, D, and E and functional dependencies:
1. B → E
2. E → A
3. A → D
4. D → E

Show that the decomposition of this schema into {AB}, {BCD}, and {ADE} is lossless. To do this, you should apply the algorithm (Testing for Nonadditive Join Property) introduced on page 546 of the prescribed text (7th edition). (Labeled algorithm 15.3)

Question 4

Consider the following functional dependencies over the attribute set A,B,C,D,E,F:
1. A → C
2. C → DE
3. A → D
4. AD → EF
5. B → AC
6. E → F

Find the minimal cover, then decompose the universial relation into lossless 3NF. Make sure that you document each step of the algorithm.

Question 5 Consider the following SQL Query:

SELECT fname, lname, pname
FROM WORKS_ON, PROJECT, DEPENDENT, EMPLOYEE
WHERE EMPLOYEE.ssn = DEPENDENT.essn AND EMPLOYEE.ssn = WORKS_ON.essn AND PROJECT.pnumber = WORKS_ON.pno AND EMPLOYEE.sex = 'M' AND DEPENDENT.sex = 'F';

a) Construct an initial (i.e. Canonical) query-tree representation of this query. Make sure that your query tree is presented neatly using a graphics manipulation application such as xfig, InkScape or draw.io. These applications are available on turing for you to use.

b) Show how your canonical query-tree from part a) can be optimised using The Heuristic Algebraic Optimisation Algorithm (page 730 of the 7th edition text) . Make sure that you show your query tree after applying each step of the algorithm. As in part a), make sure that your diagrams are constructed using a graphics manipulation application such as Xfig, InkScape or draw.io. These applications are available on turing for you to use.

Attachment:- Database Management Systems.rar

Reference no: EM132308872

Questions Cloud

How does the author suggest research results : How practical does this work seem to you? How does the author suggest research results could be applied and how do you believe they could be applied?

What program can be created to help the transgender patient : Research Paper - Transgender Care and the Health Care Professional. Transgender patients face an extreme amount of resistance and discrimination within.

Provide a new network design diagram in powerpoint format : COMP247 Data Communications Assignment - Macquarie University, Sydney, Australia. Provide a new network design diagram in PowerPoint format

Calculate the balance in connecttas work in process : ACC200 INTRODUCTION TO MANAGEMENT ACCOUNTING T119 : King's Own Institute- Describe when it is appropriate for a company to use a job costing system.

Design a relational database schema for the data provided : COSC210 - Database Management Systems - University of New England - Discuss insertion, deletion and modification anomalies and illustrate with simple examples

Identify a research or evidence-based article for treatment : Identify a research or evidence-based article that focuses comprehensively on a specific intervention or new diagnostic tool for the treatment of diabetes in.

Describe how the new tool or intervention may be integrated : A descriptive and reflective discussion of how the new tool or intervention may be integrated into practice that is supported by sound research.

What you need for the next phase of your career : A professional Portfolio is a collection of documents that provides evidence of your education, skill sets, accomplishments, goals, competencies, professional.

Describe the impact given had on nurse engagement : Discuss how an individual can use effective communication techniques to overcome workplace challenges, encourage collaboration across groups, and promote.

User Account

All Pages