N5 DDD - Databases (unfinished)

N5 Database Design & Development

By G Alexander-Doyle

Database Design & Development - Analysis​

​End user & Functional requirements

Before we begin to design a database we need to determine what the user and functional requirements are. These can be defined as follows: 

​

End-user requirements - ​These are the tasks that the end user (the person(s) who will be using the database) would wish to perform.  

​

​Functional requirements - ​Functional requirements should relate to the tasks that the database system will perform, usually in the form of searching and sorting (queries). Functional requirements may also provide detail around the data that must be held in the database.

Database Design & Development - Analysis​ (end user & functional requirements)

Database Design & Development - Analysis​

​End user & Functional requirements

Falkirk libraries have a database to store details on their members and stock. Some examples of end user requirements would be as follows:

​

• ​Members want to find out if a book is on the shelf by entering a title or author.

• Members want to see a list of books written by a particular author.

• Staff want to be able to add​ new book records to the database, remove old book records and make changes to existing book records.

• Staff want to be able to look up member's phone numbers to let them know that a book they have ordered has arrived to the library.

Database Design & Development - Analysis​ (end user & functional requirements)

Database Design & Development - Analysis​

​End user & Functional requirements

Falkirk libraries have a database to store details on their members and stock. Some examples of functional requirements would be as follows:

​

Database Design & Development - Analysis​ (end user & functional requirements)

​Members	Books​
​Membership Number	Barcode Number
Forename	Title​
​Surname	​Author
​Address	​Price​
Phone Number​	Publish Date
​Date of Birth	​Membership Number

• ​A search query should return any book records matching a given title or author.

• A list of books written by a particular author should be displayed in ascending order by title.

• The database will be required to store the following information about library members and books.​

Multiple Choice

A doctor's office are having a new database system installed to better keep track of patient and prescription records. The following statement is an example of what?

Reception staff need to be able to enrol patients.

• Functional requirement

• End-user requirement

Multiple Choice

A doctor's office are having a new database system installed to better keep track of patient and prescription records. The following statement is an example of what?

The database should store a patient ID, first name, surname, date of birth, address, contact number, next appointment time and next appointment date for each patient.

• Functional requirement

• End-user requirement

Multiple Choice

A doctor's office are having a new database system installed to better keep track of patient and prescription records. The following statement is an example of what?

Search for a patient using their surname.

• Functional requirement

• End-user requirement

Multiple Choice

A doctor's office are having a new database system installed to better keep track of patient and prescription records. The following statement is an example of what?

Medical staff must be able to update patient records.

• Functional requirement

• End-user requirement

Database Design & Development - Keys

​Primary and Foreign keys

Primary key – A unique identifier for a record 

Foreign key – A linking key (it is the primary key in one database table which is used in a second table). Foreign keys are identified with a *.  

 

It is important when deciding on a primary key in a database what field might give a unique value. For example, a person's name is not unique as people can share the same name. This is true for many fields and a database developer may have to add an ID field ​to act as the primary key.

Database Design & Development - Design (keys)

Database Design & Development - Keys

​Primary and Foreign keys

The Membership Number field appears in the Books table to indicate which member has checked the book out. This information comes from the Membership table so the Membership number is a foreign key in the Books table and the primary key in the Members table.

Database Design & Development - Design (keys)

Database Design & Development - E-R Diagrams

​Entity-Relationship Diagrams

An entity-relationship diagram is designed to show the relationship between database entities (tables) along with their attributes (fields). Primary keys are identified by underlining them and foreign keys have an asterix (*).

Database Design & Development - Design (E-R Diagrams)

Database Design & Development - E-R Diagrams

​Entity-Relationship Diagrams

The type of relation determines the referential integrity of a relational database. Relationships can be one - one or one - many. In this case it makes sense for the relationship between the Membership table and Books table to be one - many because members of the library can check out multiple books at a time. The nature of the relationship should also be described in an E-R diagram.

Database Design & Development - Design (E-R diagrams)

Database Design & Development - E-R Diagrams

​Entity-Relationship Diagrams

Sometimes E-R diagrams are represented as shown in the image below however they still contain the same information.

Database Design & Development - Design (E-R diagrams)

Fill in the Blank

A head teacher and school have a One to _____ relationship?

Fill in the Blank

A pet owner and pets have a One to _____ relationship?