Databases: Introduction

From Wikibooks, open books for an open world
Jump to: navigation, search

UNIT 3 - ⇑ Databases ⇑

Databases Primary keys →


Table[edit]

In our database we will store information about 'things', or more technically, entities. To store data about things we use Tables. Each table will store data about a particular thing. Let's look at two examples:

Database: Crime in a local area
Tables: Crimes, Victims, Criminals, Police Officers

And looking at another example that you might be familiar with, a social networking site:

Database: Social Network
Tables: People, Relationships, Messages, Pictures
Exercise: Picking Tables

List the tables you might need to store a database on a football League

Answer :

Team
Player
Match
Stadium
Referee

List the tables you might need to record details for an online store

Answer :

Staff
Products
Receipts
Customers

Attribute/Field[edit]

Now we have gotten to grips with recognising the main tables we need to store in each database, we need to recognise what fields of data will be stored in each of these tables. The data items to be stored are called the attributes. Taking the Criminal database as an example:

Table: Criminal
Attributes:
Name: String
Date of Birth: Date
Number of scars: Integer
Home town: String

Note that for each of the attributes we are listing the attribute name and the field type, when building a database it is very important that we get the correct field type to prevent errors when entering data. Let's take a look at another example from the criminals Database

Table: Crimes
NI Number: String
Description: String
Type of crime: String
Severity: Integer
Dateof Crime: Date
Victim ID: Integer
Criminal ID: Integer
Exercise: Picking attributes

For a social networking site, what attibutes and data types would you include for the table of people

Answer :

Name: string
Date of Birth: Date
Gender: string (character might also work here)
Public profile: boolean
Personal description: String

For a social networking site, what attibutes and data types would you include for the table of relationships (the table that stores 'friend' 'sister' 'father' etc)

Answer :

Person1 ID: integer
Person2 ID: integer
Type of relationship: string
Date created: Date

Records[edit]