Transcript SQL DDL - University of Kent

WELCOME aboard SA0951a
Database Design and
Implementation
SA0951a Aim/Learning Outcomes

Aim
–

Outcomes
–
–
–
2
…An extensive experience in designing and
implementing advanced database applications.
Critically analyse application requirements and
design appropriate conceptual database models.
Demonstrate an understanding of databases using
a mainframe DBMS, including database
programming techniques.
Evaluate the different approaches to data
manipulation using SQL demonstrating one of the
options within an application.
July 15
Materials and Delivery




3
All materials are held on the
BlackBoard/WebCT site for this module
Check each week to see what’s coming up
Downloads available
– Self-assessments, lectorials, labs etc
WebCT has a discussion forum if you would
like to “air” something with your classmates
(keep it politically correct and academic
though!)
July 15
Delivery Schedule


Lectorials, tutorials, labs all on Friday
Except………..
– This week and weeks 6 and 10
– In weeks 6 and 10
 lecture
will be 1-2 3004
 Lab will be in 4534.1. at 3-5
Tutorials are NOT affected
This is because the deliverers are attending a
database workshop in weeks 6 and 10 on
fridays!
–

4
July 15
What are we trying to teach you?

To go through the process of database
development from inception to delivery
– Conceptual design
– Translation to a logical design
– Implementing design
 Building
 Querying
 Programming
 Testing
5
testing testing!
July 15
How are we going to do it?


6
We encourage and expect interaction in classes
– So, don’t just listen, actively engage with us
– We’ll make tutorials interactive so you have time to
assimilate ideas and practice with colleagues
– So some group activity will be encouraged
We expect therefore
– Your participation and contributions with your peers
– To keep up the pace and develop your own level of
enquiry
July 15
Assessment



7
……… is twofold
A paper test in week 13 will examine the early stage of
database design issues
– This is 40%
A coursework submission & viva in early January
(week 17) before term 2 starts will examine the
practical issues of querying and programming aspects
– This is 60%
– The lab work will prepare you for this
July 15
Reading & Study



8
You will learn at different paces but should aim
to be up-to-date by the start of the following
week
AT the end of each lectorial we shall
recommend reading/practice to keep you on
track
Aim to spend 10 hours or so each week on the
module
July 15
We start with Relational Modelling




9
The relational model is underpinned by
mathematical set theory
It arranges data in a database into a set of 2-D
logically related TABLES.
– Each table contains many attributes
– Each attribute contains data
If your relational model is badly designed then
you will encounter anomalies
We start therefore by considering how NOT to
design a database!
July 15
Why is this Single Table design poor?
10
Student
1
Age
18
Sex
m
Module
SA0951
Title
DB
1
18
m
SA0954
OO
2
25
f
SA0951
DB
3
33
f
SA0956
JAVA
• we INSERT, MODIFY and DELETE data
• And we HATE to duplicate data and/or lose it
unintentionally 
• So, in groups, consider the effects of the
following:
• Insert a new student studying SA0951
• Delete student 3
• Modify the age of student 1
July 15
What have you learnt?



11
That anomalies will occur if you don’t design your
data model correctly
What we really need are lists of students and
modules in our database like this
But can you see a problem even with this?
– What information is lost?
Student
1
Age
18
Sex
m
Module
SA0951
Title
DB
2
3
25
33
f
f
SA0954
SA0956
OO
JAVA
July 15
How do we make the model relational?




12
The previous example had two UNLINKED
tables and therefore we could not model things
like students taking many modules
To link them we need to consider KEYS
EVERY table in a relational model has a
PRIMARY KEY
A link is formed when a primary key is matched
to a FOREIGN KEY in another table
July 15
Key definitions



13
Candidate key
– A unique identifier for a table (there may be
more than one)
– The key can be composed of any number of
attributes
Primary key (PK)
– The chosen candidate key
Foreign key (FK)
– A key that matches a primary key
July 15
Over to you!
Student
Module
grade
1
SA0951
18
1
SA0954
14
2
SA0951
15
3
SA0956
9
Choose a PRIMARY KEY for this table by considering
the candidates. Explain why you chose it.
14
July 15
OK , so now we need to LINK


15
Think of a link as having a “1” end at the
primary key and a “1” or “many” at the foreign
key end.
From the example, ask yourself ……….
– How many modules could a student take?
– How many students could any module have
on it?
July 15
Here’s the model, what are the PK and
FK’s that link the tables together?
Student
1
2
3
16
Age
18
25
33
Sex
m
f
f
Module
SA0951
SA0954
SA0956
Student
1
1
Module
SA0951
SA0954
grade
18
14
2
3
SA0951
SA0956
15
9
Title
DB
OO
JAVA
Remember you need to
identify the PK for each
table and then the foreign
keys that match a PK.
You have already found
the PK for the bottom
table.
July 15
We are well on the way now ……



17
We just need a few extra rules now for the
keys. We call them integrity constraints
ENTITY integrity
– A PK cannot have duplicate or null entries in
it
REFERENTIAL integrity
– Every FK entry MUST have a matching
entry in the related PK
– An FK can be entirely null, but not partially
null
July 15
Lets expand our student model …
refresh your memory of the keys first
Student
1
2
3
Age
18
25
33
Sex
m
f
Student
4
1
2
3
Module
Title
SA0951
DB
SA0954
OO
VB
18
SA0956
JAVA
Module grade
SA0951
18
SA0954
15
SA0957
9
Where are the violations
of integrity constraints?
Check each of the null
entries as part of the
exercise
July 15
When we store data ………


19
We store them as a particular TYPE
– TEXT for character data
– NUMBER for integer and real numbers
– DATE for the calendar
Which of these would you use for the student
model attributes?
– Justify your choice for each
July 15
Final group exercise



20

We want to model customers paying invoices
by instalments
Here are the attributes for the model:
– Customer Number, customer name,
postcode, invoice number, invoice date,
invoice bill amount, date and amount paid
for each instalment.
You need to decide on:
– The number of tables and their attributes
– The PK’s and FK’s in the model
We can continue with this in lab if not finished
July 15
Directed Reading

Find the chapter on relational modelling in any
database book
– Connolly and Begg “Database Solutions”
– Connolly and Begg “Database Systems”
– Ritchie “Relational database principles”
– Rob et al “Database Systems”
– Any many many more …….
– The internet will have many links to the subject
matter too ……….
–
21
http://www.utexas.edu/its/archive/windows/d
atabase/datamodeling/rm/overview.html
July 15
NEXT lecture & Lab



22
Lecture
– Is at 1-2pm Thursday 24th in 3004
– The remaining lectures will be on fridays 1-2 in 3004
except weeks 6 and 10. Remind you of this later.
– NO LECTURES ON MONDAYS now – ok?
The labs:
– Start Thursday 24th 3-5 in 4534.1 (also weeks 6
and 10)
– Again they will be on fridays 2-4 except weeks 6
and 10.
Tutorials
– You should be in a Group
– These run 11-12 and 12-1 on fridays, always.
– The room is either 3512.1 or 2021 – CHECK!
July 15