2023年11月25日

数据库代写｜COMP 3005 Assignment 4

这是一个加拿大的数据库作业代写

Functional Dependencies and your Project Database

In this assignment you will get practice with functional dependencies and present your term project database using them.

The assignment is based on the video lectures posted: Feb 16, Feb 28, Mar 7 on functional dependencies, normal forms, minimal covers and representing ER models with functional dependencies.

Problems 1,2, and 3 are to give you practice designing relational tables using only functional dependencies and minimal covers to achieve 3NF tables.

Problems 4 Is to repeat this process but for your term project database.

For each problem model the problem using only functional dependencies and then ensure that, if necessary, they are nomalized to 3NF. It might well happen that your initial dependencies are already a minimal cover representing 3NF if mapped to tables. It might also happen that you need some normalization.

In problem 3 and 4 we are viewing table design as starting with a list of attributes that someone wants to store in a database and then using functional dependencies to express relationships and constraints among those attributes. Then a minimal cover of those dependencies is found and that is used to form the 3NF tables.

These problems is based primarily on sections on functional dependencies and normal forms in the course notes.

An entity A,B,C,D,E can be represented by the functional dependency A,B->C,D,E

A weak entity A,B,C,D that is a weak entitity of strong entity E,F,G can be represented by the functional dependency A,E->B,C,D

There is a little trick to dealing with N:N relationships as a functional dependency. If X and Y are in an N:N relationship and Z is an attribute of that relationship then you can describe it as X,Y->Z. But if X and Y are in an N:N relationship that has no attributes then you can describe that is X,Y->temp. Then do the design and at the end just remove the column temp. For example, if Projects have many Employees and Employees work on many Projects then you can captures that as a functional dependency: ProjNum, EmpNum ->temp. But if the database also keeps track of the number of hours the employees work on each project then you can captures that as: ProjNum,EmpNum -> Hours.

The reason this trick is necessary is because algorithms that do normalization are allowed to remove trivial dependencies. ProjNum,EmpNum->ProjNum,EmpNum is trivial because the RHS is a subset of the LHS and will be removed. But if it is removed a table [ProjNum,EmpNum] will never be formed. So do the design with ProjNum,EmpNum -> temp.

Finally be careful because if several entities have the same attribute name you will have to assign different names in the functional dependencies. When establishing a foreign key relationship to implement a relationship by “adding columns to an existing table” you might have to choose non-confilicting attribute names.

Problem 1 : Movies ER Model

Consider a Database that keeps track of scenes filmed for different movies. A movie uses a screenplay (or story) which is broken down into scenes. The movie will also have the same scenes because it is a movie of that screenplay. Not all screenplays in the database become movies, but every movie is of a particular screenplay.
Also, a screenplay is used for only one movie. That is, there are not two different movies made of the same screenplay. Scenes have a story-location where the story takes place and a filming-location where the filming will actually be done. Each scene has some actors that appear in that scene. Actors have a name, phone number, address and agent that represents them. A scene can be filmed more than once (maybe the actor forgot their lines). Each filming of a scene is called a “Take”. The movie is typically created by using the best take of each scene and putting them together. Below is an E-R diagram that captures these requirements.

Using the proposed E-R diagram provided, answer the questions related to designing the tables. (Note: thick lines denote weak entities or mandatory participation in relationships.)

Also in your functional depdencies use TakeNo and SceneNo instead of Take# and Scene# (i.e. avoid the # character).

 

R1.1 [2 marks] Provide a set of Functional dependencies that completely captures all the features in the situation depicted by the ER model.

R1.2 [2 marks] Provide a minimal cover for the set of functional dependencies. (Your answer from R1.1 might already be a minimal cover but you need to check and make sure.)

R1.3 [2 marks] Based on your minimal cover find a dependency preserving, 3rd normal form set of tables to use for your database that captures all of the data intended by the E-R model. Show for each table in the decomposition, its key and the functional dependencies that apply to it (that is, map to it).