Database

 A simple database

• The simplest kind of database is a flat file, consisting of information about a single entity
• Definition: An entity is a category of object, person, event or thing of interest about which data needs to be recorded
• For example you might hold data about club members or concert venues

Entities

• You may have thought of these entities:
  • Customer
  • Guide or Product
  • Subscription
• Other entities that could be considered include customer order, subject, author (of a revision guide)
• We will keep it simple and just consider Customer, Product and Subscription.

Writing an entity description

• This will be a database system, called RevisionSubs
• Each entity in the database has attributes
• The entity descriptions can be written in this format:
  • Customer (custID, title, firstname, surname, email)
  • Product (productID, title, subject, level, price)
  • Subscription (subID, startDate, endDate)

Entity identifier (primary key)

• Each entity needs an identifier which uniquely identifies a particular record.
• In a relational database, the identifier is known as the primary key.
• It is underlined in the entity description:
  • Customer (custID, title, firstname, surname, email)
  • Product (productID, title, subject, level, price)
  • Subscription (subID, startDate, endDate)
• If there is no natural attribute for a primary key, one should be introduced.

Composite primary key

• Sometimes two 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.

Relationships between entities

• The three entities are linked, or related.
• There are three possible ways in which two entities may be related:
  • 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.
• What is the relationship between Customer and Subscription?
• What is the relationship between Product and Subscription?

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.

E-R diagram

• There is a one-to many relationship between Customer and Subscription
  • One customer may have several subscriptions, but a particular subscription belongs to only one customer.
• There is a one-to-many relationship between Product and Subscription
  • One product may appear on several subscriptions, but a subscription is for only one product.

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

Creating a relationship

• To create a relationship between Customer and Subscription, we need to include custID in the entity description of Subscription
• Subscription (subID, startDate, endDate, custID )
• ProductId also needs to be included in the entity description of Subscription

• Subscription (subID, startDate, endDate, custID, productID)
• custID and productID are foreign keys in Subscription, shown in italics • A foreign key always goes on the “many” side of a relationship