Databases

DETERMINE RELATIONSHIPS AMONG THE DATABASE TABLES

 Database design

 Identify the tables – Main objects

 Determine the fields – Records you want to store for each table/entity in the table
 Determine data types for the fields - What are you storing? How best to represent it?
 Determine the primary keys - Fields that are unique identifiers for each object/table
 Determine relationships among the tables – How do the objects relate to one another in the reality?
 Identify and remove redundancy - Avoid repetitions that can cause problems
 Table Relationships

 In order to create a Query that can extract data from more

than one table, the tables must be LINKED
 How? Relationships
 How a particular table relates or connects to one or more other
tables
 About real-world links between data items, not simply created
within databases
 Prevent duplication
 Prevent redundancy
Redundancy

 What is redundancy
 A central aim of Database Management Systems is to avoid redundancy
 Having the same data in more than one place (duplication), which causes:
 wastage of disk space
 wastage of user time
 difficulties in updating correctly – hence the possibility of inconsistent data
 decreased operational speed
 Referential Integrity (1)

 Referential (adj) -­From Reference (n) – The state of being related or referred: with reference to; in reference to.
 Integrity(n)
 -The state of being unimpaired; soundness.
 -The quality or condition of being whole or undivided; completeness.
 Referential Integrity – a property of a database meaning that things that refer to or are connected to one another are
sound, reliable, unimpaired – i.e. trustworthy as data sources
 Within a RDMS this means that:
 The data contained in a primary key field used in a table relationship must be matched in that same field in the secondary table.
 In other words, data entered in a field that is used to link two tables must match from one table to another.
 In simpler terms, a username must match with the password or no access is granted
 Referential Integrity (2)

 For example: In a database of family members with a table for each member, if we enter A as a spouse of B, we
should also enter B as a spouse of A.
 Similarly, if we remove one end of the relationship we should also remove the other.
 Makes sure there are no orphan records (records that don’t relate or connect to anything else)
 What is database validation?

 Checking for violations of the rules set up when you create a table
 All the details in the Field Properties section of the Design View are rules for data entry
 Protection – you cannot enter the wrong type of data
 Referential Integrity also keeps you from deleting data that is needed in another table
 Validation keeps the database clean
 Some of the rules:
 The legal data type
 The legal length (number of characters in a field)
 Is the field required?
 Is there a default value?
 Has the primary key been set?
 Relationship Types...

 There are 3 types of relationships in relational database design:

 One-to-One - A row in table A can have only one matching row in table B,
and vice versa.
 One lecturer teaches one class, each class taught by only one lecturer
 One-to-Many (or Many-to-One) - This is the most common relationship
type.
 In this type of relationship, a row in table A can have many matching rows
in table B, but a row in table B can have only one matching row in table A.
 One lecturer teaches many classes
 Many-to-Many - In a many-to-many relationship, a row in table A can
have many matching rows in table B, and vice versa.
 A many-to-many relationship could be thought of as two one-to-many
relationships, linked by an intermediary table.
 each lecturer teaches many classes – each class taught by many different
lecturers
 Relationships: one-­‐to-­‐many (1) The primary key
in one table is a
foreign key in
another

One Winery can produce

many Wines (one to many)
 Relationships: one-­‐to-­‐many (2)

There is ONE Winery

Code for each winery in
the Wineries table

But there are many wines

from a winery, so in the
Wines table the Winery
code will be repeated once
for every wine from that
winery
Implementing Relationships: How MS Access links
tables (1)
In the starting database
window, select the • To view your table relationships, click Relationships on the Database
Relationships button Tools tab.
• The Relationships window opens and displays any existing relationships.
• If no table relationships have been defined and you are opening the
Relationships window for the first time, Access prompts you to add a
table or query to the window.
• On the Database Tools tab, in the Relationships group,
click Relationships.
• If the database contains relationships, the Relationships window appears.
If the database does not contain any relationships and you are opening the
Relationships window for the first time
• On the Design tab, in the Relationships group, click All Relationships.
Implementing Relationships: How MS Access
links tables (2)

To create a relationship
between two tables, click
on the primary key of one
table that occurs as a
foreign key in the other
table (in this case Winery
Code and Winery) and
drag and drop the primary
key right onto the foreign
key in the other table.
Implementing Relationships: How MS Access
links tables (3)
 Check the Enforce Referential
Integrity box.
 Note the relationship type at the
bottom of the dialog box.
 Click the Join Type button if you
want to change the default
properties of the relationship.
 Click Create to create the
relationship
 Implementing Relationships: How MS Access
links tables (4)

 A line (or “Join”) will appear joining the two

tables in Query Design view
 This represents the permanent relationship you
have created between the two tables.
 It is a one-to-many relationship
 One Winery can produce many wines, but each
wine can only come from one winery
 Why create table relationships?

 Table relationships inform your query designs - To work with records from more than one table, you often must
create a query that joins the tables. The query works by matching the values in the primary key field of the first
table with a foreign key field in the second table
 Table relationships inform your form and report designs - When you design a form or report, Access uses the
information it gathers from the table relationships you have already defined to present you with informed choices
and to prepopulate property settings with appropriate default values.
 Table relationships are the foundation upon which you can enforce referential integrity to help prevent orphan
records in your database.
 An orphan record is a record with a reference to another record that does not exist
 You cannot enter a value in the foreign key field of the related table that does not exist in the primary key of the primary
table.
 You cannot delete a record from a primary table if matching records exist in a related table
 You cannot change a primary key value in the primary table, if that record has related records
Creating a complex Query (3)

 All Selected tables are shown in

the Query Design Window.
 If a permanent relationship has
been created between the two
tables, it will be visible as a join
between the two tables
 Creating a complex Query (4)

 If no permanent relationship has been defined,

you can create one in the same way that you
would when creating a permanent relationship
 Dragging the primary key of one table onto its
occurrence as a foreign key in the other table.
Creating a complex Query (5)

 Right-­clicking on the join line

allows you to choose between
deleting and editing the
relationship
Creating a complex Query (6)

 Add fields to the query in the

normal way
 You can however select
fields from both tables.
 Creating a complex Query (7)

 This query is equivalent to the MySQL statement it creates behind the scenes:
SELECT Wines.Name, Wines.Type, Wines.[White/Red/Rose], Wineries.Name,
Wineries.[Telephone Number]
FROM Wineries INNER JOIN Wines ON Wineries.[Winery Code] =
Wines.Winery
WHERE (((Wines.[White/Red/Rose])Like“White"))
ORDER BY Wines.Name;
 This can be viewed by choosing SQL View from the View Toggle
 Saving a Query

 Run the query by pressing the Run

button.
 The results of the query look like this.
 Remember to save your query with
a name that will indicate what data
it extracts.
 Many-to-Many Relationship

 Now let’s look at the relationship between a Products table and an Orders table
 A single order can include more than one product
 On the other hand, a single product can appear on many orders.
 Therefore, for each record in the Orders table, there can be many records in the Products
table
 In addition, for each record in the Products table, there can be many records in the Orders
table.
 Note that to detect existing many-to-many relationships between your tables, it is
important that you consider both sides of the relationship.
 To represent a many-to-many relationship, you must create a third table, often called a
junction table, that breaks down the many-to-many relationship into two one-to-many
relationships.
 You insert the primary key from each of the two tables into the third table.
 As a result, the third table records each occurrence, or instance, of the relationship.
 Order Details table
 Many-to-Many Relationship

 Problematic to build into a tabular database

 Massive tables
 Millions of nulls
 This makes database administrators
 Surprisingly common
 How do deal with these?
 Normalisation!
 what is normalisation in database ?

 Database normalization is a database design technique that reduces data redundancy and eliminates undesirable
characteristics
 Like Insertion, Update and Deletion Anomalies (eliminate data anomalies).
 Normalization rules divides larger tables into smaller tables and links them using relationships.
 The purpose of Normalization in SQL is to eliminate redundant (repetitive) data and ensure data is stored logically.
 Cost?
 speed of retrieval – joins slow things down
 becomes an issue in large datasets with many tables normalised so as to require many joins.
 Database normalization is the process of structuring a relational database in accordance with a series of so-called normal
forms in order to reduce data redundancy and improve data integrity.
 Normalization is the process of organizing data in a database / A “process for assigning attributes to entities”
 Creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the
database more flexible by eliminating redundancy and inconsistent dependency.
 Redundant data wastes disk space and creates maintenance problems
 Relationships: many-to-­many (1)

 A record in table A (STUDENTS) may have many matching relationships in table B (SUBJECTS); e.g. one
student may take many subjects AND
 A record in table B (SUBJECTS) may have many matching relationships in table A (STUDENTS); e.g. one
subject can be taken by many students
 How to resolve this?

 You show many-to-many relationships by creating a Third Table called a Join or Junction
table.
 It is not possible to show many-­to-many relationships with only 2 tables
 You must have at least 3
 A junction table contains both the primary key columns of the two tables you want to
relate.
This is called a
junction table(third
table)
 Junction Table
Course
details only

Student details
only

 Many-­to-­many relationships let

you relate each row in one table to
many rows in another table, and
vice versa.
 A junction table contains the
primary key columns of the two
tables you want to relate.
Joint Table
[Student + Course
details]
Creating a Many-­to-­many Relationship

 Define the primary key of a junction table as both of the primary keys from
the other two tables.
 Do this by holding down the Control key while you select the columns, before you
click the key icon.
 This is called a composite key.
 If a primary key consists of more than one column, duplicate values are
allowed in one column, but each combination of values from all the
columns in the primary key must be unique.
 Define a one-to­‐many relationship between each of the two primary tables
and the junction table.
 The junction table should be at the "many" side of both of the relationships you
create.
 Your First 2 Tasks

 A person may have many addresses

 An address can house many people
 How would you resolve this dilemma?
 Suppose you had a database to track
lecturers, classes, rooms, and periods
 What would you do to ensure that:
 A lecturer is in only one room each
period
 A lecturer teaches only one class each
period
 A room has only one class each period
 A room has only one lecturer in it
each period
 What is wrong with this?

 GID not repeated for each group member so

effectively has nulls
 – e.g. UID 007 and 006 have no GID
 GRP_NAME in table repeatedly
 People in more than 1 group have their
username, first and last name and so on
repeated
 – redundant, waste of space, can lead to
update inconsistencies, etc
 Normalisation

 systematic way of ensuring

database structure is suitable for
general­‐purpose querying
 Free of certain undesirable
characteristics relating to
anomalies and potential data loss
when performing
 Insertion
 Update
 Deletion
 More Definitions

 Repeating Groups
 Those data attributes which repeat in combination with another.
 The presence of certain rows in a table implies the presence of certain other rows
 Also called a multi­‐valued dependency
 Partial Dependencies
 Dependant on only part of the Primary Key
 Transitive Dependencies
 Dependant on non-key attributes
 Prime/Key Attribute
 Those on which others have dependencies
Example 1: Normalise this table
 1NF (First Normal Form)

 Remove “repeated groups”

 Ensure all key attributes are defined
 All attributes are dependent on the primary
key(“on the key”)
 2NF

 Requirements:
 1NF
 No partial dependencies
 has to be the whole key
 3NF

 Requirements:
 2NF
 No transitive dependencies
 Nothing but the key
 Summary of Normalisation

NORMAL FORM TEST REMEDY (NORMALIZATION)

1NF Relation should have no non-atomic attributes or nested relations. Form name relation for each non-atomic attribute or nested relation.
Decompose and set up a new relation for each partial key with its
For relations where primary key contains multiple attributes, no non-key attributes dependent attributes. Make sure to keep a relation with the original
2NF
should be functionally dependent on a part of the primary key. primary key and any attributes that are fully functionally dependent on
it.
Relation should not have a non-key attribute functionally determined by another non-key
Decompose and set up a relation that includes the non-key attribute(s)
3NF attribute (or by a sets of non-key attributes) i.e., there should be no transitive dependency
that functionally determine(s) other non-key attribute(s).
of a non-key attribute of the primary key.

Relation should not have any attribute in Functional Dependency which is non-prime, the Make sure that the left side of every functional dependency is a
BCNF
attribute that doesn’t occur in any candidate key. candidate key.

The relation should not have a multi-value dependency means it occur when two
4NF Decompose the table into two sub tables.
attributes of a table are independent of each other but both depend on a third attribute.
The relation should not have join dependency means if a table can be recreated by joining
multiple tables and each of the tables has a subset of the attributes of the table, then the Decompose all the tables into as many as possible numbers in order to
5NF
table is in avoid dependency.
Join Dependency.