Databases
Fundamentals of databases
As we learned at GCSE, a database is made up of tables which represent a type of person, thing or object that we wish to store data about e.g. the members table below for storing details of members of a group. Tables are also
referred to as entities.
Each table is made up of fields which are headings under which data is stored in a table e.g. Surname shown below by the green box is a fied about which data is stored for each member.
A record is
the full set of data for all fields for one entry in a table, shown by the red box below.
A primary key is a field or fields that uniquely identify a record. In this case ID is the primary key as it is possible two members
could have the same name, so an ID is needed to uniquely identify a user. Email could also have been used as a key field as thye are unique.
A foreign key is a key field form one table used in another table to form a link
e.g. if this was a members table for a library then the member ID would also appear in the loans table as a foreign key.
Types of relationship
Relationships between entities can be defined as one to one, one to many or many to many.
One to one relationships include a country has one leader e.g. the PM in the UK; a book has a uniue ISBN
One to many relationships include a
doctor has many patients but each patient has just one doctor; an order contains many products but each product belongs to just one order
Many to many relationships include a teacher has many students and a student has many
teachers; A swimmer may compete in many events and each event conatains many swimmers
Entity descriptions
Entity descriptions are a text based way of representing tables in a database. Entity descriptions are in the following format:
entity_name(Attribute1, Attribute2, Attribute3, Attribute4)
An attribute or attributes that
make up the primary key are underlined in an entity description. So for a teacher in a music school there might be a table to store personal details or the instruments taught e.g.
teachers(TeacherID, Forename, Surname, Phone, Email)
instruments(InstrumentID, Instrument, Type)
Relational Databases
A relational database is made up of a number of inter-related tables/entities. Tables are linked using foreign keys. A foreign key is the primary key of one table used in another table. If we thought about a music school, if
each teacher teaches just one instrument, then the link can be made by adding the instrument ID to the teachers table as shown below.
teachers(TeacherID, Forename, Surname, Phone, Email, InstrumentID)
instruments(InstrumentID, Instrument, Type)
In a relational database we can represent one to one and one to many relationships but we must break down many to many relationships using link tables. Lets now consider that each of our teachers can teach more than one
instrument. This gives us a many to many relationship where each teacher may teach many instruments and each instrument is taught by many teachers. We can no longer simply add an instrument to the teacher table or a teacher to
the instrument table. Instead we introduce a link table.
teachers(TeacherID, Forename, Surname, Phone, Email)
instrumentteachers(TeacherID, InstrumentID)
instruments(InstrumentID,
Instrument, Type)
