Please enable JavaScript.
Coggle requires JavaScript to display documents.
4.2 Database Concepts (Secondary Key (The primary key field is…
4.2
Database Concepts
simple database
Flat file database has everything in one table usually on one entity
When may it be suitable to use a flat file database
When storing only limited amounts of data
When your not familiar with DBMS
Security is not a big concern
Entity
An Entity is any item in the system about which data is stored, e.g. Supermarket, Stock, Manager, etc.
Primary Key
Each entity needs an identifier which uniquely identifies
a particular record
It is underlined in the entity description:
Customer (
custID
, title, firstname, surname, email)
Product (
productID
, title, subject, level, price)
Subscription (
subID
, startDate, endDate)
Composite Primary Key
Sometimes 2 or even more attributes are needed to uniquely define a record
For example, in a customer order consisting of many different order lines, each order line may be uniquely identified by the two attributes orderNumber and orderLine
OrderLine (
OrderNumber, OrderLine
, ProductID, …)
OrderNumber, OrderLine is a composite primary key
Secondary Key
The primary key field is automatically indexed so that any particular record can be found very quickly
In some databases, searches may often need to be made on other fields
In the product table,
Product (productID, title, subject, level, price)
if searches often need to be made on title or subject, either or both of these fields could be defined as a
secondary key
They would then be indexed for faster lookups
(Positive) Allows records to be quickly retrieved
(Negative)-Takes up more hard disk space
Relationships between entities
Link Title
One-to-one e.g. Husband and Wife
One-to-many e.g. Mother and Child, School and Pupil
Many-to-many e.g. Actor and Film, Recipe and Ingredient
Entity relationship diagrams
An entity relationship (E-R) diagram is a graphical way of representing the relationships between entities
We can say, for example, that one school has many pupils, or many pupils attend one school
Database structure
Each entity is represented by a table
Tables in a relational database are commonly referred to as relations
A database contains one or more relations
A relation has rows, each row containing one record
The columns in the relation each contain one field (i.e. attribute) belonging to the records
Foreign Key
A Foreign Key is an attribute which creates a join between 2 tables
It is the primary key in the first relation
Referential integrity
Referential integrity means that no foreign key in one table can reference a non-existent record in a related table.
For example, it should not be possible to add a subscription for a customer with custID C100 if there is no record for customer C100