Embedded Files
DB section has been updated to add some SQL code.
Course Content Specification
Course Content Specification
Describe and exemplify entity-relationship diagrams with three or more entities, indicating:
entity name
attributes
name of relationship
cardinality of relationship (one-to-one, one-to-many, many-to-many)
What is a flat file database?
What is a flat file database?
A flat file database is when all of the data is contained within a single table.
An example is shown below which contains information about Designers and the Items of furniture that they have designed.
A sample flat file database
Structure of a Flat file Database
Structure of a Flat file Database
The entire table has a collection of records with each record having a collection of fields.
A record is a row in a database that is a collection of fields of a particular object.
A table is a collection of records which each has numerous fields. Can be referred to as an Entity when being designed.
A field is an area on a record which contains an individual piece of data.
Problems with a Flat file database
Problems with a Flat file database
An anomaly is when there is something that is different or unexpected from normal. A flat file database will exhibit a few of these.
Data Duplication
Data Duplication
In a flat file database we usually have to enter the data repeatedly, this is known as data duplication. This causes un-necessary data to be stored and also increased the chance of data being entered incorrectly. The example below demonstrated this.
The above table has numerous data duplication and this has led to inconsistency
Insertion Anomaly
Insertion Anomaly
When we insert data we may not have all the data to hand. An insertion anomaly is when we need other related data such as in the example below, this is known as an insertion anomaly.
The company begins to use a new designer but as yet they have not designed any items
Deletion Anomaly
Deletion Anomaly
A deletion anomaly is when we aim to delete one piece of data but we inadvertently remove other related data. In the example below if we delete the Dining Table
We no longer stock the glass dining table but when we delete the record we also delete information we have about the designer D Cobb
Update/Modification Anomaly
Update/Modification Anomaly
An update anomaly or modification anomaly is when to change some details we have to update the same information multiple times.
If I Donnelly changes their email address or phone number we need to change the same information multiple times
Relational Databases
Relational Databases
Relational Databases help us to combat a lot of the problems with flat file databases. To do this we will split the information into two tables and link them together using a common field.
Primary Key
Primary Key
Think of a register, what if there are two pupils in a class with the same names? How do we differentiate between the two? We need to include a field that can uniquely identify a single record. This is known as a primary key.
In the example below the field ItemID is the primary key. The value will never be repeated for any other record. An entity is said to have entity integrity if it has a unique non null identifier.
Foreign Key
Foreign Key
When you have two linked tables you will use the primary key from one table as the foreign key in another table to create the relationship between the two tables. A foreign key will always be a non null primary key from another table. This is known as referential integrity
So as Designer ID is a foreign key when we enter a Designer ID field in the Item table it is checked (looked up) to see there is a matching value in the Designer Table. If there is not then the data will not be allowed to be saved.
Entity Relationships
Entity Relationships
When we are designing a database and we group the fields that we are storing into Entities (which will become tables eventually). We need to establish the relationship (or cardinality) between the entities. There are three possible types of relationship.
One To One (1:1)
One to Many (1:M)
Many to Many (M:N)
Examples of Different Entity Relationship Types
Examples of Different Entity Relationship Types
Page updated
Report abuse