3.1.5: Relational Database Concepts

3.1.5: Relational Database Concepts A Summary

The aim of this presentation is • To provide you with an overview of the aspects that make up a relational database. • This includes: • Tables • Records • Fields • Data types • Keys • Composite primary key • Secondary key • Entities • Data dictionary • Parameter queries

Basics • Imagine a database is a filing cabinet. • Each drawer stores information about a particular thing. • A drawer is known as a table.

Basics • Imagine a database is a filing cabinet. • Each drawer stores information about a particular thing. Student Table

Basics • Imagine a database is a filing cabinet. • Each drawer stores information about a particular thing. Teacher Table

Basics • Because each table stores data about one particular thing we call it an entity.

Basics • Because each table stores data about one particular thing we call it an entity. Entity: Student

Basics • Because each table stores data about one particular thing we call it an entity. Entity: Teacher

Basics • Each drawer contains a record and each record is about one thing, for example: • One particular teacher • One particular student Teacher Record Forename: Andy Surname: Dolinski Department: ICT DOB: 01/01/1784

Basics • Each record is broken down into fields. Teacher Record Forename: Andy Surname: Dolinski Department: ICT DOB: 01/01/1784

Basics • Each field has its own data type. • A data type represents the type of data that should be stored in that field. Teacher Record Forename: text Surname: text Department: text DOB: date/time

Data types • There are lots of different data types and each one is suitable for storing a different type of information. • Some of the more common data types include: • Text • Used for storing any type of data that uses characters, numbers, symbols etc. • Number • Used for storing a number that you would perform a calculation on. • AutoNumber • Generates a number automatically. • Date/Time • Used for storing dates and times. • Currency • Used for storing money values. • Boolean • Used for storing one of two possible options (Yes/No, Boy/Girl, On/Off).

Primary Key • Each record should have a primary key. • A primary key is a piece of data that is stored in a field which is unique to each record. • In this example, the student’s username is their unique key. No other student can have the same username meaning it is unique. Student Record Username: broberts2 Forename: Bob Surname: Roberts DOB: 01/01/1994

Composite Keys • You can sometimes make a unique value based on the data in two of the fields. • Instead of using a username to make a primary key, a school could combine a student’s name with the first line of their address to form a unique key. Student Record Forename: Bob Surname: Roberts Address:Cody Way Student Record Forename: Sam Surname: Roberts Address:Peanut Rd

Composite Keys • This method sometimes causes problems though. • Using this example, you can probably see that if a student had a brother or sister their unique values wouldn’t be so unique anymore... • Which is why it is sometimes better to just stick with a separate field such as username! Student Record Forename: Bob Surname: Roberts Address:Cody Way Student Record Forename: Elliott Surname: Roberts Address:Cody Way

Secondary Keys • These are attributes that are designed as an alternate means of accessing data. • This attribute is not necessarily unique either. • For example, a library my use ISBN numbers as a primary key, but they may also store the book’s Dewey number. • You could still use the Dewey Decimal System to find a book but the database might not use this as a primary key. • This is because they may use the same Dewey number for a different book if it is located on a different floor.

Secondary Keys Floor 2: A2 Revision The library has two floors. The first floor is used to store books relating to AS subjects. The second floor is used to store books relating to A2 subjects. Each subject has a main code. ICT is 150, Maths is 151 etc. Each subject has a number of books. ISBN: 978-0340966518 DDS: 150.01 Floor 1: AS Revision ISBN: 978-0340958285 DDS: 150.01

Data Dictionary • Whenever you create a database you need to keep a second ‘database’ which stores data about the structure and contents of the main database. • It should include data about: • What tables the database has and what their relationship to other tables is (more on relationships later). • The structure of each field • Validation rules • The number of records being saved • Etc…

Queries • We know that tables store data…and in some database applications, such as MS Access, you can do some basic sorting (e.g. alphabetical). • But sometimes you need to find certain data based on different criteria. • For example, an internet service provider may want to find all customers who have not paid their bills yet. • They could then go on to filter these results further by looking for all customers who have had their first warning and still not paid.

Queries All Data A query can be used to find, sort and filter data. This data can then be used to find new data, e.g. total number of outstanding payments. It can also be used for things like mail merge, e.g. sending second warnings to customers. Not yet paid Already had a warning

Simple and Complex Queries • A simple queries uses one criteria value, for example: • Find all male students. • A complex query uses two more criteria values, for example: • Find all male students who were born after 1995.

Parameter Queries • A parameter allows you to dynamically query data. • They allow you to cut down on the number of queries you have to create. • For example, you may have one query which finds all male students and another query that finds all female students. • If you create a parameter query you could have the query ask you what criteria to use. • If you type in Male it will find all male results. • If you type in Female it will find all female results.

3.1.5: Relational Database Concepts