Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Unit 10, Database Development

No description
by

Sarah Toms

on 5 December 2016

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Unit 10, Database Development

Unit 10, Database Development
Unit outcomes
A - Understand the uses of and tools/ techniques used in databases

B - Design a relational database

C - Develop and test a relational database

D - Review the finished relational database.

Typical Spreadsheet
Improving Flat Files
Group data into smaller tables with a defined relationship between them

At the design stage we can use an Entity Relationship Diagram ERD

Answers
Steps
Why do we use relational databases?

Designing a relational database

Implementing a relational database

Testing a relational database

Lets look at a typical spreadsheet
The whole spreadsheet is a table

A spreadsheet is a one table database, sometimes known as a flat file database

There are problems with using just one table

Labels
What problems can you identify with the spreadsheet.
Think about what the issues that might occur as more and more data is added
In Pairs.....
Problems with the Flat File
The same ID number can appears in multiple rows. You cannot retrieve a unique record with the ID number

Entities may not have an ID number.

Details are repeated – DATA REDUNDANCY, wasting space.

Data can be INCONSISTENT as it is stored more than once

Multiple records have to be updated. This is an AMENDMENT ANOMALY

Multiple records have to be deleted. This is a DELETION ANOMALY

More than one record may need to be added. This is an ADDITION ANOMALY

Data may not be ATOMIC.

What groups of data can we make from our example flat file
ERD -
Entity Relationship Diagram
The relationship is that:
-A dog can only have one owner
-One owner can have many dogs
-This is a
“one to many”
relationship

Owner
Dog
One to Many
1:M
each owner may own many dogs
Owner
Dog
Individual Task

5 Mins
List the Databases you have interacted with in the last week.

If you have time list how you interacted with them
In Small Groups....
If you were designing a database for students at a college, what entities might you need.
10 mins
1.
Feedback to the group and decide on a set of entities.
2.
How might the tables relate to each other
One to Many
1:M
Owner
Dog
each owner may own many dogs
One to One
1:1
Staff
Car
each staff owner may have one company car
Possible Entities for College database
Student
Course
Module
Assignment
Exam
Tutor
Databases you may have interacted with
Banks
Super Market
Library
College Databases
Government Agency
staff Records at work
Facebook
shopping Online




Whole Group...
10 mins
Extra Time?
?? mins
3.
List the most important attributes for each entity
Examples of
Attributes
within a database for a Student
Entity:

Student ID
Forename
Surname
Address
Postcode...


As a whole group we will take from each of the solutions, discuss the best and create a final Entity Relationship Diagram for the college
Individually...
10 mins
Answer the following questions:
Which of these would you use a database for?

a: Keeping details of hospital patients
b: Making a presentation
c: Forecasting a financial budget
d: Creating memos, letters and other documents
1.
Which if these statements is NOT true?

a: The term DBMS refers to the data
b: The term database can refer to the software as well as the data
c: Microsoft Access is a DBMS
d: A database is a collection of data
2.
5.
A primary key...

a: Is normally a real value such as a someones name
b: Is used to unlock the database file
c: Uniquely identifies a record
d: Can have duplicate values
4.
A table...

a: Answers questions about your data
b: Contains all the data about a certain thing or object such as a student or patient
c: Is a complete set of data items for one particular thing or object
d: A way of outputting data suitable for printing
3.
Within a database fields are:

a: Always unique
b: Used to create database applications
c: The columns within a database table
d: A complete set of data items for one particular thing or object
Within a database of cars, what would be a good candidate for the primary key?

a: The owners name
b: The car's registration number
c: The car dealer who sold the car
d: The cars make and model
6.
Database forms are primarily used to?

a: Store the data itself
b: Fomat data for output to a printer
c: Allow input and output of data on the computer screen
d: Answer questions about the data
7.
Which of these is an advantage of a computer database over a paper filing system?

a: It can quickly sort data in any order required
b: It is very easy to use will little training required
c: It is cheaper than a filing cabinet
d: It cannot store very large volumes of data
8.
Which of these is an advantage of a computer database over a spreadsheet?

a: It has better graph drawing facilities
b: It is better at numerical calculations
c: It is easier to set up
d: It can store multiple related tables
9.
Which of these is a disadvantage of a database?

a: It can store very large volumes of data
b: They can be highly complex and require expert technical support
c: Searching for data can be a very long process
d: Data cannot be easily shared
10.
Note the question number and your choice

Once time is up swap with person next to you

Check the answers


Answers to Questions
1:
2:
3:
4:
5:
a
a
b
c
c
b
c
a
d
b
6:
7:
8:
9:
10:
Leaning Outcomes
By the end of this session you will have:
Demonstrated understanding of terminology
Started to understand queries and how relationships support them
Understood first Assignment tasks
Completed Collaborative prezi

By the end of the session you may have:
Been able to answer SQL query question
Started Task 1 Assignment 1
Database Terminology Quiz
http://www.teach-ict.com/gcse_computing/ocr/databases/entities/quiz/ontarget_entities.htm
Go to:
Complete the quiz

Make sure you record your results.
Enter your Name and result on the paper provided

15 mins
Task 2
Relational Database Queries
1. Follow the Link below to the wiki
Download the Relational Database exercise
2. In Pairs: Use the tables given to produce ERD
3. Whole Group: Final agreed ERD
hints: they are not obvious but there are primary and foreign keys listed. use these to work out the relationships. Remember Primary Key is the one side and equivalent Foreign key is many side (Crows foot)
4. Individually: Carry out the query exercise
Assignment 1: Why Relational Databases
Task 3:
Assignment Introduction
https://wiki.hastings.ac.uk/groups/computingbtecnationaldiplomayear2/wiki/a5361/UNIT_18_Database_Design.html
Either:
Complete the collaborative prezi if you haven't done so

Or:
Start on Task 1 of Assignment 1
10 mins
5 mins
15 mins
30 mins
Entity Relationship Diagram

- Modelling real data in an information system is never perfect.

- Compromises often have to be made. However the patterns above will work in most cases.

- An Entity-Relationship diagram (a.k.a. Logical Data Structure) is generally a combination of these patterns.

Conclusion

Husband

Wife

Many other supposed One-to-one examples are often simply a single table, One-to-Many, or Many-to-Many.. Consider the example:


Product
ProductID
ProductDetails

Order
*CustomerID
*ProductID

Customer
CustomerID
ContactDetails

The rule is that the Link Table, which usually describes something real, like an Order, Supply, Loan, is in the middle and has the many ends of the relationship.

In its simplest form, its Primary key is a composite of the Foreign keys that link it to the other tables.

Resolving Many-to-Many Relationships

Tutee
StudentNo
*StaffNo

StaffMember
StaffNo
ContactDetails

These are much more common. For example the Staff_member has many Tutees.



The link is between a Primary key at the One end, and a Foreign key at the Many end.

The Foreign key value at the Many end is always a copy of the Primary key value at the One end.

One-to-Many Relationships.

StaffMemberPrivate
StaffNo
SalaryDetails

StaffMemberPublic
StaffNo
Contact Details

Partitioned Table
One-to-One Relationships only appear occasionally in relational databases. For example these two tables have the same Primary key.

One-to-One Relationships

OrderLine
*OrderNumber
OrderLineNo
*ProductID

Order
OrderNumber
*CustomerID

Customer
CustomerID
ContactDetails

Product
ProductID
ProductDetails

or using Order-Line_No rather than the composite key

Resolving Many-to-Many

Wife
WifeID
*HusbandID

Husband
HusbandID

Husband
HusbandID

Wife
WifeID

Marriage
CertNo
*WifeID
*HusbandID

So it could be modelled as either




thereby recording each relationship,
or perhaps (in a polygynous society)




– a polyandrous would be the mirror image)

Resolving one to one relationships

StaffMember
StaffNo
Contact Details
SalaryDetails



Logically they describe the same entity – StaffMember – and in a preliminary design would be just one table:






However when implementing it, and satisfying the Non-functional requirement that the database follows the Data Protection Act 1999, it may be easier to partition the data and to password protect a separate table of Private Information.

One-to-One Relationships

Couple
MarriageCertNo
WifeID
HusbID
Etc………..

Husband
MarriageCertNo

Wife
MarriageCertNo

If both entities had the same key, as in the first example





perhaps the real entity is a Married Couple and it should be modelled as:

Resolving one to one relationships

OrderLine
*OrderNumber
*ProductID

Order
OrderNumber
*CustomerID

Customer
CustomerID
ContactDetails

Product
ProductID
ProductDetails

- Multiple transactions on one order

- Both models fall down whenever the Customer expects to handle several transactions on one Order, e.g. buying food in a supermarket

Resolving Many-to-Many

Product
ProductID
ProductDetails

Order
*CustomerID
*ProductID

Customer
CustomerID
ContactDetails

Product
ProductID
ProductDetails

Customer
CustomerID
ContactDetails

In a standard Relational Database these must be “decomposed” (i.e. broken down)





into two or more One-to-Many Relationships with a “Link Table”

Resolving Many-to-Many Relationships

Product
ProductID
ProductDetails

Order
OrderNo
*CustomerID
*ProductID

Customer
CustomerID
ContactDetails

Product
ProductID
ProductDetails

Order
*CustomerID
*ProductID
OrderDate

Customer
CustomerID
ContactDetails

The first model produces a problem if a customer comes back and orders the same product again.
It may be resolved by adding a Date Attribute to the Primary key



Resolving Many-to-Many

Only one record of a relationship for each person.

The model above would be unable to cope with the real world:
where there is polygamy (only the first Wife or first Husband could be recorded)
widowhood/divorce/annulment & re-marriage (which relationship would be recorded?)

Alternatively it may have its own Unique Order Number
Borderline cases

An Auto Hire company may normally hire cars one at a time, so use the simpler model with one Hire link table.
If someone wants to hire several cars they will have to complete several input forms to create the links.
If they find that this happens often they may decide to move to the more complex model (when customers hiring just one car will be presented with the possibility of multiple entries that may confuse them!)

New Technique: Link Phrases
Well formed link phrases can assist in understanding a relationship from both sides
Product
ProductID
ProductDetails

Order
*CustomerID
*ProductID

Customer
CustomerID
ContactDetails

CUSTOMER AND ORDER RELATIONSHIP
Each
CUSTOMER
can make
one or more
ORDER
Each
ORDER
will be made by
one and only one
CUSTOMER
Rules:
One relationship consists of two sentences for both sides
All sentences begin with the word:
Always use descriptive words to describe:
the relations is always or

Each
will be made by
one or more
one and only one
TASK: Natlib Library ERD
30 mins
Natlib is a small private library specialising in natural history books. They have a collection of titles available for loan by registered readers, free of charge.
Each reader is allowed to borrow up to eight books at a time. Loans are to be recorded against particular book copies, rather than their title, as Natlib may have several copies of any given book.
When all copies of a book are already out on loan a reader may wish to place a reservation for it. Each reservation is recorded against a book title. When a copy of the title is subsequently returned, Natlib will place it to one side, record which copy is to satisfy the reservation, and notify the reader that it is ready for collection.

1. In pairs: Read through the scenario and highlight possible Enitites. List them.

2. As a whole group check correct entities

3. Once we have decided upon the entities create link phrases for possible
relationships: Make sure you form the link phrases correctly

4. Draw you final ERD Diagram resolving any M:M's

5. If you have time label the diagram with descriptive words and relationships e.g. 1:M




By the end of this session you will have:
Fully understood basic relationships
Understood resolving Many to Many's
Complete and ERD for NatLib

You may also have:
Used notation on your ERD diagram
Individual Research Task

15 Mins
What is a relational database?

What is a relational database used for?

If you have enough time what advantages can a relational database bring to an organisation?
Relational database
- is a database that uses a series of tables to store data. The tables contain related data and are connected to each other for example student and course.
Databases can be used to :
Make easy to manage records of data- as you can store, view, query, report and analyze data.
Keep data secure and safe- information stored on a database can be secured with user names and passwords a well as levels of access can be giving to specific people
People can work collaboratively- many people can work on the same database at the same time.
Carry out calculations- a database can carry out complex calculations in an instance and generate reports of data and example of this could be a monthly income report for a shop.
Activity 10.1
A small garage currently stores information in a paper-based filling system they record the following details:

Their customers.
Regular car services undertaken and car service history of the cars they see regularly.
MOT details
Cars brought in for repairs, with dates, details of faults and work done.
Invoices and payments .
Details of the different car parts held in stock.
Scenario
Garage details
A garage owner is thinking of bringing in a specialist to advise them whether it is worth converting their paper based filling system to a computerised one. Advantages that they have already been made aware is that it will produce accurate invoices and will manage payments. He can also see the possibility of improving their general record keeping.

Imagine you are a database specialist, Prepare a list of further advantages , giving some simple examples of how these relate to the garage

Large amounts of duplicated data (repeated data)
Makes data entry slower and prone to error
Wastes disk space
Can easily lead to inconsistent data
Can be hard to update
Difficult analyzing and reporting
Can have security implications as all users can see all information

Why don’t we use single table (flat file) databases?

Features and benefits of relational databases

An Entity is a business object that represents a group, or category of data
Examples:

Suppliers
Products
Students
Courses
Orders

What is an Entity?

Efficiency of storage – reducing data redundancy Simplicity

Ease of modifications – new tables can be added without putting the whole database out of action

Can represent complex relationships between objects in the real world

You can offer different ‘views’ of the database using queries to different users according to their need.

Benefits of relational databases

Tables
– store the data itself
Queries
– used to answer questions about the data. Can extract and display records which match certain criteria
Reports
– used to format the output of data from tables or queries in a user friendly way – mainly for printing
Forms
– used to input and/or output data in a user friendly way – for on-screen use

Objects within a database

Hand out Activity sheets AS1
Information separated into discrete Entities, which are represented as tables of data within a database. - A collection of data
- Based on the real world
- e.g. employee, product, order
Entities are described by a series of Fields.
- Describes the structure of the entities
- Describes the individual sets of data stored within each entity table
- e.g. product name, weight, price, etc.
Entities are connected by a series of Relationships
- Built using common field
- Represented by entity relationship diagrams (ERD's)
Features of a Relational Database
What is an Attribute?
Many to Many
M:M
Student
Course
each student may have enrol on many courses
Types of relationships
Each table of data that is stored on a database is called and entity, and each entity is related to other entities. These are called relationships there are three different types of relationships between entities within a relational database.
an example of this is:
In the garage activity that we completed , some of the customers may own more than one car and every car may have been serviced many times. this detail can be represented using a entity relationship diagram (ERD's)
Types of relationships
One-to-Many
Remember the tables of data are entities. for example, the information about vehicles will be stored in one entity table and the information about services to that vehicle will be stored in another entity table. The fact that one vehicle can have many services this is classed as a one to many relationship.
Types of relationships
Many to Many
In many situations, the relationship is many to many, for example, the garage might have a database listing spare parts and suppliers. The spare parts will be in one table and the suppliers in another table. Each spare part might be in stock at many suppliers, And each supplier has stock of many parts. Many-to-Many relationships are difficult to implement in a database, so to deal with this type of relationship , another link table is created. this table breaks down the many-to-many relationship into too one-to-many relationships.
Types of relationships
One-to-One
In some situations the relationship is one-to-one. One record in the first table corresponds to just one record in the other table. For example, in a hospital database, the patient details (such as name and address) are stored in a different table to the patients medical detials (such as the medications that they are taking) this is a one-to-one relationship because each patient has only one set of medical details. And each set of medicall details is linked to only one patient.
One-to-One relationships are not good practice and can be quite insufficient. But in some cases there may be good reasons for using this relationship; for example, if both the administration staff and medical staff need access to the database, but only the medical staff should be able to access the patients medical detials.
Types of databases
we have already previosly looked into the difference between flat file databases and relational databases.

Another difference between databases are whether they are local or on a network.
Local database
will typically be saved and stored on a single machine at home or in an office in a small companies e.g on your mobilephone or home computer.
alternatively, the database could be put onto a network to provide a wider access for many people at different locations.
Increasingly databases are being used online, working behind the scenes of websites. for example, when using amazon, you will be sure that the stock levels you see are up to date. to acheive this,the software running the website accesses the stock details in an underpinning database.
Examples of local and online/networked databases.
local databases:
Contact list on your phone
Apps or games that are not mulitplayer and do not connect to the internet via facebook e.c.t
a small hotels booking system database which is stored internally.
Online/Networked databases:
online shops
applications and games on your phone that connect to the internet and allow multi-player through a network e.g facebook,
Field characteristics

© Pearson Education Ltd 2012. Copying permitted for purchasing institution only.

- Verification is connected with validation and together they aim to make sure that the information entered is accurate and error free
- Verification takes place before validation

Validation is:
- the process of detecting incomplete or unreasonable data – use of types or input masks are forms of validation.

Verification is:
- Used to check that the data entry is accurate – entering data twice is a form of verification

Validation v verification

© Pearson Education Ltd 2012. Copying permitted for purchasing institution only.

- Used with very specific set of values

- Works with Validation Text

- Example rule set – if all staff are in either Sales, Production or Marketing then set rule as:
-‘Sales’ or ‘Production’ or ‘Marketing’

- Data entry will only allow from this set of three and will display the Validation Text if not

Validation rule

© Pearson Education Ltd 2012. Copying permitted for purchasing institution only.

- Validation is used to try and avoid incorrect data entry.

- Choosing the right TYPE is part of validation but there is more …

- Validation starts with the design of the table and the setting of the properties of each field.

- Additional validation controls can be placed on data entry forms, e.g. drop-down lists

Validation

© Pearson Education Ltd 2012. Copying permitted for purchasing institution only.

- One field within each table is normally chosen to be the primary key

- Unique identifier for each record

- Usually a numeric or alphanumeric code

- Ensures each record can be uniquely identified

Primary key

© Pearson Education Ltd 2012. Copying permitted for purchasing institution only.

-Text

-Date and/or Time

-Number

-Currency

-Yes/No
... and others

‘Types’ of types

© Pearson Education Ltd 2012. Copying permitted for purchasing institution only.

- Setting a field property to Required ensures that a user cannot leave the field blank – they must enter a value

- e.g. Surname, Postcode, Reference number

-Use sparingly

-Often seen on web data entry forms marked with *

Required values

© Pearson Education Ltd 2012. Copying permitted for purchasing institution only.

- A useful example to control the input to a limited set of values

- Validation Text is displayed if the validation rule is not followed

Validation Rule Example

© Pearson Education Ltd 2012. Copying permitted for purchasing institution only.

Normal format for date shown; this can be altered if necessary

Examples
:

- more than simble >
forces all text to uppecase

-[red]
forces text to be displayed in red

-dd/mm/yyyy
works on date/time field to force format


This affects the ways the data is displayed and/or printed. It does not change the data in the field.

Format

© Pearson Education Ltd 2012. Copying permitted for purchasing institution only.

- Text fields have a default of 255 characters

- Designer estimates the maximum length of any one value entered and sets accordingly

- Advantage: if you know a code number has only 6 characters and set field size accordingly, people cannot enter 7 characters

Field size

© Pearson Education Ltd 2012. Copying permitted for purchasing institution only.

- Data type
- Number range
- Date range
- Length of data item
- Format

To validate, you can check:

© Pearson Education Ltd 2012. Copying permitted for purchasing institution only.


Every field must have a type

- Type is chosen to reflect the data that is held in that field

- Choosing the right type is important as it:
- starts the process of validation
- makes storage efficient

Field types

© Pearson Education Ltd 2012. Copying permitted for purchasing institution only.


Date of an order – could be set to ‘today’ as most orders will be entered on the date they are created

For a student age group field – if most are 16-19 and only a few 19+ then set default to 16-19

- Any entry here will be the default value for that field

- Can be changed by the user
Examples.

Default value

© Pearson Education Ltd 2012. Copying permitted for purchasing institution only.

Example
If you set Field Size to 2 and input mask to LL you can only enter two text characters

- Makes distinctions between text and numbers
- Text set as L
- Number set as 9

Input mask

Choosing the best type is the first step in ensuring valid entries

length of number of people is set 3
Field characteristics and validation rules class activity
look at the vehicle table shown in the table activity sheet 10.1
1.
2.
3.
One feild that is missing is the date the vehicle was registered. make a list of other necessary and usefull feilds that you think should be included in this table.
Note the most appropriate data type for each feild.
The company wants to add a feild to store whether the vehicle has been sold or not. Choose and explian what data types could be used for this field. State which data type you think might be the best and explain your answer.
Unit Introduction
Hand out activity sheet 2, table design
Make a start on creating a database with tables in microsoft access [activity in SB pages 319-321]
Extra time
Full transcript