Define what the arrows point to. Each arrow has more than one definition.

 [image]

• The order of the rows have no significance.
• The order of the columns have no significance.
• Only one attribue value at each row/column intersection.
• Each row much be uniquely identifiable.

An entity is a object or concept which the enterprise reconises as being capable of an independent existence, in the sense that it can be uniquely identified.

e.g. a student, a module, an order, an employee, a product

An attribute is a property of an entity.

e.g. student_surname, module_desc, order_date, employee_no, price

A relationship is an association between two or more entities.

e.g. the entity Product may be assocated with the entity Order by the relationship ordered_on

What part of this diagram is the

• entity type?
• relationship type?

[image]

Student and Course are the entity types, in the rectangular box.

enrols_on is the relationship type, in the diamond box.

What are the enterprise rules as defined by this ERD?

[image]

*** warning, these dots may be the wrong way round. this is according to me (not prior)

A student enrols on only one course, and cannot exist without one.

A course can have zero, one or many students enrolled on it.

What are the enterprise rules for this ERD?

 [image]

A footballer scores zero, one or many goals.

A goal must be scored by one footaller.

*** this may be wrong, this is according to Prior

Decompose this ERD relationship:

[image]

We have a student and a module table linked by a many to many relationship.

student(student_no, student_name, student_addr, dob)

module(module_code, module_title)

What is the best way to represent the M-M relationship between these entities? Why would you not use another method?

By creating a joining table, as the M-M relationship needs to be decomposed.

modulechoice(student_no, module_code)

You cannot use a posting method (i.e. putting module choice in the student table, as you would be creating repeating groups and this is NOT 1NF.

Ensure that there are no repeating groups.

That is, we enfore the rule that there can be only one attribute value at each row/column intersection.

Second normal form only applies to tables with a composite key.

To achieve 2NF, every non-key attribute must be dependant on the whole key.

2NF tables also need to be in first normal form.

If it's not in 2NF, the non-key attributes that are not dependant on the whole key need to be moved to another table - together with the part of the key they are determined by.

This table type is in 1NF - now make it 2NF.

StaffDevelopment(employee_no, staff_development_date, course_title, organiser)

2NF

StaffDevelopment(employee_no, staff_development_date, course_title)

Course(course_title, organiser)

The table must have the non-key attributes mutually independent. This means that there should be no dependencies between attributes that are not keys.

Also, the table must first be in 2NF.

If a table is not in 3NF, you must remove the non-key attributes that are dependent on other non-key attributes to form another table, together with the attributes they are determined by.

This table is in second normal form. Review the table type and make it into 3NF.

JobHistory(employee_no, job_start_date, job_end_date, job_title, salary_range, department)

3NF

JobHistory(employee_no, job_start_date, job_end_date, job_title, department)

Job(job_title, salary_range)

A stronger definition of third normal form, where every determinant must be a candidate identifier.

For example, look at this table:

In this example, the rate type available is depedant on the court booked.

The table does not adhere to BCNF. This is because of the dependency Rate Type → Court, in which the determining attribute (Rate Type) is neither a candidate key nor a superset of a candidate key.

The process of deriving tables that are free of redundant data by putting a table through the rules of first, second and third normal form.

This sometimes requires moving attributtes (columns) into other tables, for example to avoid repeating groups (1NF).

Player(player_no, player_name, game_no, date_played, result)

Which of the following 4 statements is correct regarding the above table type, assuming a player can ply 1 or more games and games are held on a different date?

• It is in Boyce-Codd Normal Form
• It is not in First Normal Form
• It is in First Normal Form
• It is in Second Normal Form

The player table is NOT in first normal form, therefore it is not in 2NF or BCNF either.

Reason: repeating groups

EmployeeTraining(emp_no, course_no, date_attended, course_title)

Given the above table type and assuming each course_no is associated with one course_title, is the table in second normal form?

No, it's not in 2NF.

Reason: an attribute can be found by just part of the key, instead of having to use the entire key.

Which pair of enterprise rules correctly describe this relationship?

[image]

• A cake is made using 0, 1 or many recipes. A recipe can be used to make exactly one cake.
• A cake is made using 0, 1 or many recipies. A recipe can be used to make zero or one cake.
• A cake is made using 0 or 1 recipes. A recipe can be used to make 1 or many cakes.
• A cake is made using 0 or 1 recipes. A recipe can be used to make 0, 1 or many cakes.

[image]

• A cake is made using 0, 1 or many recipes. A recipe can be used to make exactly one cake.

*** according to prior

How would you represent this relationship with the given entities?

[image]

[image]

 By posting player# into Game (posting the 1 into the M) and keeping Player as it is.

Game(game#, date, player#)

How would you represent this relationship with the given entities?

[image]

[image]

By creating a new table:

runs(store#, staff#)

OR

runs(store#, staff#)

How would you represent this relationship with the given entities?

[image]

[image]

By creating a new table:

AllocatedTo(staff#, vehicle#)

How would you represent this relationship with the given entities?

[image]

[image]

As it's a 1-1 relationship, can post:

LectureTheatre(room#, capacity, equipment#)

but this approach can involve NULL

But, if normalising, can make new table

Fitted(room#, equipment#)

** needs confirming

A pitfall in a database design when it involves 3 or more entity types when required information cannot be accessed in a relation (table).

There are two types, a fan trap and a chasm trap.

Where there are any two 1:M relationships, with the '1' ends back to back.

For example:

 [image]

There is a fan trap between

Run (M:1) Film (1:M) Showing

 [image]

A chasm trap occurs when the path through an ERD does not exist for some entity occurences.

To fix them, the 'chasm' needs to be bridged by supplying the missing relationship.

Where is the connection trap in the ERD. Explain it, and how can it be fixed?

[image]

There is a chasm trap. Employees with no department have no link to a site, even though it's perfectly valid to have a site instead of a department.

 To fix it, add another relationship:

[image]

How would you draw an ERD with these recursive relationships?

1. An employee manages 0, 1 or many employees. An employee is managed by exactly one manager.
2. A major component is comprised by 0, 1 or many minor components. A minor component is part of 0, 1 or many major components.

Q1

[image]

Q2

[image]