Given a relation R= {A, B, C, D, E, F} and a set of functional dependencies FD’s: {AB→C, DC→AE, E→F}

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Database Systems Fall 2019

Assignment 2

Note – This assignment will be graded and is individual submission. Total of 100 points. This assignment is worth 10% of your total grade. All submissions should be made through ICON.

1. Given a relation R= {A, B, C, D, E, F} and a set of functional dependencies

FD’s: {AB→C, DC→AE, E→F}

Determine keys using the Left, Middle, Right method discussed in class. There are multiple keys, you must find all of them. [5 marks]

2. Given a relation R= {A, B, C, D, E} and a set of functional dependencies

FD’s: { CD→A, EC→H, GHB→AB, C→D, EG→A, H→B, BE→CD, EC→B }

1. Determine keys using the Left, Middle, Right method discussed in There are multiple keys, you must find all of them. [5 marks]

2. Is relation R in 3NF? If not decompose it into relations in 3NF. [5 marks]

3. Given a relation R= {A, B, C, D } and a set of functional dependencies

FD’s: { A→B, ABCD→E, EF→ G, EF→H and ACDF→EG}

Find the minimal basis for the given FD’s using method shown in class. [10 marks]

4. Consider a relation Courses(C, T, H, R, S, G), whose attributes may be thought of informally as course, teacher, hour room, student, and

Let the set of FD’s for Courses be C → T, HR → C, HT → R, HS → R and CS → G.

Intuitively, the first says that a course has a unique teacher, and the second says that only one course can meet in a given room at a given hour. The third says that a teacher can be in only one room at a given hour, and the fourth says the same about students. The last says that student get only one grade in a course.

1. Determine keys using the Left, Middle, Right method discussed in There are multiple keys, you must find all of them. [5 marks]

2. Find the minimal basis for the given FD’s using method shown in [10 marks]

3. Use the 3NF synthesis algorithm to find a lossless-join, dependency-preserving decomposition of R into 3NF relations. [10 marks]

4. Use the chase test for lossless join to tell whether we can recreate the original courses relation with the same tuples using natural join of relations from 4(c). [20 marks]

 

5. The fictional online service BitBarrow provides a repository for shared software It uses a database to track various aspects of the service, including the following.

• Users, who are identified by their personal email address and each have a registered name and nickname.

• Projects, identified by a unique project title.

• Which users work on which projects. Each user can work on several different projects, and each project may have multiple

• For each project, exactly one user who is the project

• Different kinds of project. Projects may optionally be declared as mobile, desktop, or server. Mobile projects need an identified platform, and desktop projects a named operating

Draw an E/R diagram for this situation. Create attributes that are suitable for keys, and indicate weak entity sets if needed. Make sure to indicate relevant cardinality constraints. [10 marks]

 

6.Convert the ER diagram to a relational model using E/R method, Object Oriented method and nulls

method. Write the schema for each relation in the model. [15 marks]

which relational model conversion method works best to answer the following questions and why?

[5 marks]

1. List First name and Last name of all customer who ordered a value

2. List all the orders without value

Attachments:

Expert's Answer

1092 Times Downloaded

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of