Download Understanding Primary Keys and Referential Integrity in Database Design and more Summaries Design in PDF only on Docsity!
Criteria Name Pages
P1 Explain the Features of a Relational Database. 1 - 4
M1 Explain referential integrity and the purpose of
primary keys in building the relationships
between tables.
D1 Discuss how potential errors in the design and
construction of a database can be avoided.
Course: BTEC IT Extended Diploma, Level 3 (Software
Development) – Year 1 (F1215)
Name: Richard Collins 164166
Unit: Unit 18 – Database Design
Tutor: Ali Kariz
Assignment: Assignment 1
Started: 23
rd
February 2017 23/02/
Submission: 5
th
March 2017 05/03/
P1 – Explain the Features of a Relational Database.
Features:
- Entities ; Is an object of importance or something in which we can store data for potential further
usage. Example : Employee
- Attributes ; Is the data behind an Entity also known as the “Properties” of an Entity. These are fields
which are related to the Entity defined above. Example : Employee ID, First Name, Last Name, Date of
Birth.
Sales Table
Accessories Name Phone Type Price Sold By (Employee ID)
Car Charger Android £8.99 164166
USB Lead Apple £19.99 164166
- Relationships ; Is the relation or “link” between Entities. The Links are created by using Primary Keys
on each form and linking them to a secondary key on the main form, of the same name.
Relationships are used when Entities are related to one another. Example : Employee, Phone Sales,
Accessories Sales.
- Benefits ; 1. Data that is only stored once saving space where the database is stored, this is due to the
fact that information is not repeated unlike other databases.
2.Queries of a complex nature can be carried out within the Database and its forms, making
for faster processing times.
3.Security is better and improved upon, keeping all information within a Database safe.
4.Designing a Relational Model is simplistic compared to other Database types.
5. Alternative “views” created and used by different users set to be able to access the
database, using queries.
We use keys to connect tables to each other. A primary key is used to detect and identify individual
records, Primary keys are unique in properties, such as Name. Primary keys are usually linked to
secondary or “foreign keys”, A primary key in one table is a foreign key in another table, when they
are used like this, they are known as common keys,
EmployeeID
Name
Surname
Date of Birth
OrderID
EmployeeID
ProductID
ProductID
Product
Price
Manufacturer
Entity
Attribute
Attribute Value
OLE Object - OLE Objects such as Documents or Spreadsheets are created in different
programs but are used in the database by the OLE Protocol.
Hyperlink - Usually a pathway link to a website or location on a computer that is
clickable.
Data Validation Is the entry of data into the database. Data is checked to see if it is sensible, for
example Student marks 1-100 for test, this means no less than 1 or more than 100. Other rules
include capitalisation of all names or checking to see if Date of Birth is correct (by checking to see if it
is before today
Size is relative to the size of the fields, by default Access gives 256 characters per entry but if we limit,
we save space and processing. For example; Year of Birth would be limited to 4 characters for 19—
and year of birth.
Relationships-
There are 3 types of relationships when relating to databases.
1. One to One , One table to another. Assuming Manager manages one shop only. Example
would be Manager to Shop.
2. Many to Many , Many table to many tables. Example would be Employee to Customer. 3. One to Many , One table to many tables. Example would be Manager to Employees.
One to One
One to Many
Many to Many
Normalisation Is breaking a flat file, which has large amounts of data including many aspects of
repetition, into much smaller manageable tables in a third normal form. Data populated on the third
normal form would become atomised and ensure that there is no repletion or duplication or
redundancies.
Un-Normalised form – [UNF] When a table in a database has repeating attributes, then it is
an Un-Normalised form.
First Normal Form - [1NF] A table is a First Normal form is we remove all repeating attributes.
Second Normal Form – [2NF] A table is a Second Normal Form if there are no partial
dependencies.
Third Final Form – [3NF] A table becomes a Third Normal forms if it meets the criteria of the
First and Second Normal form together.
Manager
Shop
Manager Employees
Customers Employee
UNF 1NF 2NF 3NF
CustomerName
CustomerSurname
CustomerGender
CustomerAddress
CustomerPhone
CustomerEmail
EmployeeName
EmployeeSurname
EmployeeGender
EmployeeAddress
EmployeePhone
EmployeeEmail
OrderNum
OrderDate
ProductName
ProductDiscription
CatagoryType
CatagoryDiscription
CID
CutomerName
CustomerSurname
CustomerGender
CustomerAddress
CustomerPhone
CustomerEmail
OID
CID
OrderDate
EmployeeName
EmployeeSurname
ProductName
ProductDiscription
CatagoryType
CatagoryDiscription
CID
CutomerName
CustomerSurname
CustomerGender
CustomerAddress
CustomerPhone
CustomerEmail
OID
CID
PID
OrderDate
PID
ProductName
ProductDiscription
EmployeeName
EmployeeSurname
CatagoryName
CatagoryDescription
CID
CutomerName
CustomerSurname
CustomerGender
CustomerAddress
CustomerPhone
CustomerEmail
OID
CID
PID
EID
OrderDate
EID
EmployeeName
EmployeeSurname
EmployeeGender
EmployeeAddress
EmployeePhone
EmployeeEmail
PID
CatID
ProductName
ProductDiscription
CatID
CatagoryName
CatagoryDescription
Key
Wholly Dependent
Field
Partially Dependent
Field
Non-Key Dependent
Fields are gone
Referential integrity
Referential integrity
is keeping consistency between the forms created. This is keeping the data
between the primary and foreign key as consistent, ensuring they remain
matched between the forms. An example of this would be a record of deliveries
and a record of what is out for sale, you would ensure that the data would move
to ensure that an item has gone from delivery to out to sale. If there are only 10
items delivered then there cannot be more than 10 items going out onto the
shop floor sale, as no more exists from the delivery form.
Keys
Keys are used to identify fields.
There are 2 main types of keys commonly used in databases.
◦ A primary key – a mandatory unique identifier which can be linked to Foreign keys to create
relationships
◦ A Foreign key – a key which links from one form to another via foreign key to primary key.
Keys have to be used throughout a database, otherwise there would be no way to identify tables
and no way to link them through relationships.
Many to Many
Employee
EmployeeID
EmployeeFN
EmployeeLN
EmployeeDOB
EmployeeAddress
Order
OrderID
EmployeeID
ProductID
ProductName
CustomerID
CustomerName
Product
ProductID
ProductName
ProductDate
ProductLocation
In referential integrity is
enforced you can only make
reference to Employee,
Product or Customer that
exist in the Link table.
Customer
CustomerID
CustomerFN
CustomerLN
CustomerDOB
CustomerAddress
N
N
N
D1 – Discuss how potential errors in the design and construction of a database can be
avoided.
Identify and Explain a range of common errors in database design and construction. Explain how
these types of errors can be avoided
Accidental deletion of Files, fields, Data.
When a person, or people, are handling Files, Fields or Data there is always the constant risk
that an accident could happen which leads to its deletion, or as sometimes happens,
corruptions of data or files. When this happens it can mean that all the data entered or data
stored is completely lost, which can impede on a company’s efficiency and become time
consuming to fix this error by recreating the lost data to their best ability, often this is not
even possible and the data is lost permanently, a lot like when a Hard Drive becomes
corrupted and a person loses his/her files, the same principle applies.
There are many ways a person, or company, can ensure that Accidental deletion is avoided,
one of these is to assign the correct Access rights to the correct employees. Instead of
having every employee at the same user access level, distribution among the employee
access rights can ensure that only data that the employee needs to be working with is at
risk. An example would be to have the Employees as Standard users and the Managers as
Administrator users. This will ensure that the employees cannot delete data outside of the
restrictions set on the user account, limiting them to forms or data which they only need
access too, such as specific day sales table or customer details. The managers would have a
larger access, but because there are fewer managers than employees, less risk of deletion of
data by multiple users trying to make changes to multiple forms.
Another way to avoid this is to ensure there is always a backup present of all data collected,
including all files. This would mean that should someone accidently delete something that
shouldn’t have been, it can be restored from the backup. It would be crucial for the backup
to be at least daily to ensure that data is protected at its best level but there may be reason
to have it back up every few hours as a more precautionary tactic.
Incorrect usage of data type
When creating databases, or anything related, it is important to use the correct data types
for the correct data being inputted. If the wrong data type is used then the data is not going
to be usable to the best of its ability, for example if a field for Numbers is declared as string,
such as Costs, there would be no way to perform mathematics with this field of data, as you
cannot add, subtract, multiply or perform any mathematics with fields of string data. This
means that the user would have to manually calculate anything he/she needed to do, such
as total costs, VAT or discounts, when if they had used the correct type, Number, they would
have been able to create a formula to use the data inputted and perform the mathematics
for them.
The same applies for shortening time when using the database or entering data into the
system, using the wrong data types can lead to hassle and time consumption which could be
avoided, for example if a person has to enter a value for Yes/No by using “Y” or “N” instead
of simply choosing to use a Boolean method instead, which would generate 2 possible
answers for the user to select from, a much simpler method and much more effective for
business purposes.
Invalid Data
When dealing with data, it is possible to get what is known as Invalid Data, which is data that
is not defined as sensible. It may not be either right or wrong but it is not sensible to the
field in which it is being entered into.
How to Prevent
When dealing with Invalid Data or data that is not sensible there are many ways to counter it
from being entered.
The first option to ensure data is checked correctly, can be a Range check. A range check will
check to see if the data entered is between 1 figure and another. For example, if used in an
exam environment, the person taking the exam could only score between 0 and 100, it is
impossible to score over 100. This means any data entered over 100 would be blocked or
rejected by the database.
Another method would be to check for the type of data being entered into the field, if it is
not the correct type for the field, such as trying to enter Text into a Numbers field, then it
would be rejected or blocked by the database.
Another method is called a Consistency check and is considerably a good idea for when using
codes of kinds in any environment. For example, on an Employee record, you may need to
record their National Insurance number, all of these start with “NI” so you make the field
force the user to start the field with those two letters, which will mean that all data saved
will be consistent in the way it is laid out. Each field of data will all have NI before it in every
record saved for every employee (Ex. NIXXX- 000 - 123)
A final form of checking data is a Validation rule. This would be useful in ways to prevent
customers from registering with a company that are below a certain age. For example, some
Game companies have these on their websites which ask the user to input their age and if it
is below the age set for the game, it kicks them out of the site. The same principle as
applicable to Database fields, if a new customer was registering for an account with a
company and they need to be of a certain age first, when their year of birth is entered it will
either Block them form making an account or allow them if they are over the set age by the
company. This is quite common with companies that use a credit/debit system, as their
customers have to be over 18 to take out lines of credit with the company
