Construct a relational schema for the er-diagram

Assignment Help Database Management System
Reference no: EM13330822

Question 1.

Consider that a journalist wants to create a simple database for covering the 2010 Tour de France cycling race. The race consists of a number of stages identified by a starting and destination city. Each stage is completed on a single date, and goes over a specific number of kilometers. Riders (first name, last name, and date of birth, which together are unique) are employed by a team which is identified by a name and is managed by the team boss. The team boss is not a rider and is identified by his/her name. A team needs to have at least one rider to enroll in the race. Each rider must belong to one and only one team in the race. No two teams are allowed to have the same boss. A team also assigns one specific rider who acts as the team leader in the race. Riders participate in a number of stages (not necessarily all, as they could give up after a number of days) and it is possible that a rider is absent from all stages.

For each stage that a rider participates in, his total time spent from start to finish is recorded. Stages are either Mountain, Flat, or Team Time Trial stages. There are no other kinds of stages. For mountain stages the database keeps track of the grade (= dificulty level), the number of mountains in it, and the best climber (a rider). For a fiat stage we record the number of intermediate sprints. For a team time trial we record which team won the stage.

(a) Draw an Entity-Relationship diagram for this database using UML notation. Be sure to include all the entities mentioned above, together with attributes (including primary key attributes), relationships and multiplicity constraints. Note that generalization/specification will be involved in the ER diagram.

(b) Construct a relational schema for the ER-diagram. Make sure that you correctly translate Specialization and Many-to-Many relationships. Please follow carefully the following guidelines when you finish this question:

 Please ONLY include the following entities in the ER diagram: Team, Rider, Stage, Boss, Mountain, Flat and TeamTimeTrial. The last three
entities correspond to the three stage types. It is possible that there are more than one relationship between two entities in the diagram;
 Please only use the attributes given in the instruction. Please do not make up any other attributes yourself or use other names for the attributes;  Please only use the drawing tools (e.g., Microsoft Word or Visio) that are available for drawing up the ER diagram. It is not allowed to use those tools for automatic generation of ER diagrams; Always highlight the primary keys for the entities created in the ER dia-
gram and the relations transformed from the ER diagram.

Question 2.

Consider the following relation schema for table R:

R(ENo;DNo;PNo;EName;DName;PName;Edob;Dmanager;PCity;PCountry; hours; rate) Attributes starting with "E" refer to Employees, those starting with "D" refer to Departments, and those with "P" to Projects. Employees, Departments, and Projects are identified by unique numbers. Each department has only one manager, but an employee can be managers for difierent departments. The number of hours and hourly rate of pay for a stafi to carry out a project are determined by the stafi and the project. There may be multiple projects that
are conducted in a department and multiple departments can be involved in a single project as well. Any employee can be afiliated with and work for any departments in any projects at the same time. Names are not generally unique; however, cities are uniquely named within and across all countries.

Answer the following questions:

(a) Identify the Functional Dependencies in R. Be sure to maximize the number of attributes on the right hand side (RHS) and minimize the number of attributes on the left hand side (LHS) of each FD. You should exclude any trivial FDs in your answer.

(b) Identify the Super Key of R based on the Functional Dependencies. You need to use the concept of attribute closure to identify the key. Intermediate steps in this process should be summarized.

(c) Assume that R is in 1NF. Now normalize the relation to 2NF, 3NF, and BCNF. Be sure to indicate the FDs you are removing at each step, and why. Just giving the decompositions in each of the three Normal Forms is not suficient.

While it is recommended to answer this question on just one page, you should use no more than two pages for this question.

Reference no: EM13330822

Implement the entity relationship model

Implement the entity relationship model provided in the sample solution of Assignment - Map the ERD into a set of relations in at least Third Normal Form (3NF). You must ensu

Draw an erd containing the order and customer entity

Draw an ERD containing the order and customer entity types connected by a 1-M relationship from customer to order. Choose an appropriate relationship name using your common

Find a good explanation of cascading and use that

Cascading can be difficult to understand. Explain it in a way someone not versed in databases could understand. If you research and find a good explanation and use that, be

How do e-commerce websites use database technologies

How do e-commerce websites use database technologies to improve sales and track customer-buying and customer-searching habits? In your discussion, provide at least three exa

Explain how sales of individual items would be entered

How a system could improve efficiency? How a system could improve accuracy? How sales of individual items would be entered? How the database would store the data compared to t

Computing functional dependencies

Compute the functional dependencies which exist in following table. After determining the functional dependencies, transform this table to an equivalent collection of the ta

List all pair of products names which are in same city

List the names of agents that placed an order for customer C003 or customer C006. List all pair of products names that are in the same city, along with their city name.

Identifying all attributes and decide primary key of invoice

Considering the following example of Invoice, you will design relational database by following the step suggested. Please, answer each step. Step1: Identifying all attributes


Write a Review

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd