DBMS with SQL
desg
lecture_03_s12014v2.zipLecture_03_S12014v2.ppt
Welcome to
CIS 2002: DATABASE DESIGN AND IMPLEMENTATION
Semester 1, 2014 – LECTURE 3
*
*
*
4.wmf
COMMONWEALTH OF AUSTRALIA
Copyright Regulations 1969
WARNING
This material has been copied and communicated to you
by or on behalf of The University of Southern
Queensland pursuant to Part VA of the
Copyright Act
1968 (
the Act
).
The material in thi
s communication may be subject to
copyright under the Act. Any further copying or
communication of this material by you may be the subject
of copyright protection under the Act.
Do not remove this notice.
USAGE OF SLIDES
Use of these lecture slides is restricted to teaching staff and students enrolled in this course. All students who use these slides should have acquired the prescribed texts.
These slides are for the personal use of students on the Study Desk only.
Students should not copy slides, allow third parties access to the slides or distribute the slides.
Copyright of these slides vests in the university and, where applicable, Cengage Education or Pearson (publishers of the textbooks)
*
*
READINGS – WEEK 3
- Study Book, Modules 3 and 5 (module 5 is not directly examinable)
- Selected Readings 2.1 and 2.2 (Will be used for next 4/5 weeks)
- Casteel, chapter 8
*
*
Online Test
- Due date: opened no 19 March (close 31 March)
- Only ONE attempt – so please ensure you are prepared before attempting this test.
- 10 Multiple Choice Questions generated from a large pool of question set – so every student is likely to get different questions with different options.
- Only one answer option is right – select the best option.
- 30 minutes to complete the test – plenty of time.
- Attempt weekly quiz for week 1, 2, 3 and the practice quiz several times before attempting the test.
- When you are consistently doing well in the practice quiz, you are ready to give the online test.
*
*
THE RELATIONAL MODEL
- CORE PRINCIPLES OF THIS COURSE
- Helps you understand DB theory and practice
- Helps you understand SQL
- DBMS FUNCTIONALITY IS BUILT UPON MODELS AND THEORY – RELATIONAL MODEL, RELATIONAL ALGEBRA.
*
*
Where does the Relational Model fit in with Data Modelling?
Steps in the data modelling process
- 1. Draw ERD’s
- 2. Use ERD’s to construct relations
- 3. Use relations to construct tables
*
*
EXAMPLE
CLIENT SPECIFICATION
- At Company A, customers often place many orders but a single order is for only one customer.
- Each order consists of several product. A product may be ordered several times.
*
*
Data Analysis: understanding business rules
- A customer places many orders
- An order is placed by one customer
- An order may have many products
- A product is for many orders
- NOTE: These rules are purposely not well specified – we will return and critique them later in the semester
- HOW CAN WE REPRESENT THIS IN A DIAGRAM?
*
*
Database Design:
*
*
Using ERD (ENTITY RELATIONSHIP DIAGRAM) for data modelling
CUSTOMER
ORDER
PRODUCT
What happens next?
- We construct relations to represent the ERD. Relations are just lists of entities with attributes listed.
- We use the relations to construct the tables in the actual (physical) database
- For the purposes of this course, another name for a set of relations is an entity list
*
*
Tables
- Relational database is a collection of tables
- Heading: table name and column names
- Body: rows, occurrences of data
*
*
Student
*
Partial Student table:
- 5 columns
- 3 rows
- Real student table: 10 to 50 columns; thousands of rows
Convention:
- Table names begin with uppercase
- Mixed case for column names
- First part of column name is an abbreviation for the table name
- Upper case for data
|
StudentID |
LastName |
Major |
Level |
GPA |
|
123-45-6789 |
WELLS |
IS |
Undergrad |
6.00 |
|
124-56-7890 |
NORBERT |
FIN |
Postgrad |
5.70 |
|
234-56-7890 |
KENDALL |
ACCT |
Postgrad |
6.50 |
Relational Model Terminology
- A relation is a design of a table - it also has columns and rows.
- Only applies to logical structure of the database, not the physical structure.
- It could be called “a table waiting to happen”
- Relational Database is a collection of normalized relations with distinct relation names.
*
*
*
Relational Model Terminology
- Attribute is a named column of a relation.
- Domain is the set of allowable values for one or more attributes.
- Tuple is a row of a relation.
- Degree is the number of attributes in a relation.
- Cardinality is the number of tuples in a relation.
*
*
*
Alternative Terminology
*
*
| Table-oriented | Set-oriented | Record-oriented |
| Table | Relation | Record-type, file |
| Row | Tuple | Record |
| Column | Attribute | Field |
*
Table-oriented: familiar
Set-oriented: mathematical
Record-oriented: IS staff
Terminology is often mixed: table, record, field
RELATIONAL MODEL AND DATA MODELLING
- The data modelling approach used in this course (ERD) is highly related to the relational model
- Another approach is object-oriented design using UML
- ERD still dominates database arena and even application arena (think of those legacy systems) but UML being used more with tools such as Java
- Technically oriented professionals should master both – there are many similarities
*
*
Two Relations – Branch and Staff
Source: Connolly and Begg: Database Management – Excellent technical resource
*
*
Database Relations
- Relation schema
- Named relation defined by a set of attribute and domain name pairs.
- Relational database schema
- Set of relation schemas, each with a distinct name.
*
*
Properties of Relations
- Relation name is distinct from all other relation names in relational schema.
- Each cell of relation contains exactly one atomic (single) value.
- Each attribute has a distinct name.
- Values of an attribute are all from the same domain.
- Each tuple is distinct; there are no duplicate tuples.
- Order of attributes has no significance.
- Order of tuples has no significance, theoretically.
*
*
Relationships
*
*
STUDENT
COURSE
ENROLLMENT
What is the relationship here?
One student enrols in many courses.
A course has enrollments from many students.
An enrollment is for a student in a course!
*
Shown by matching values
- First Student row (123-45-6789) related to 1st and 3rd rows of Enrollment table
- First Offering row (1234) related to 1st two rows of Enrollment table
Combine tables using matching values
Relational databases can have many tables (hundreds)
Follow matching values to combine tables:
- Combine Student and Enrollment where StdSSN matches
- Join operation
Relationships
*
*
STUDENT
COURSE
ENROLLMENT
One student enrols in many courses.
A course has enrollments from many students.
An enrollment is for a student in a course!
*
Shown by matching values
- First Student row (123-45-6789) related to 1st and 3rd rows of Enrollment table
- First Offering row (1234) related to 1st two rows of Enrollment table
Combine tables using matching values
Relational databases can have many tables (hundreds)
Follow matching values to combine tables:
- Combine Student and Enrollment where StdSSN matches
- Join operation
Relationships
*
*
STUDENT
COURSE
ENROLLMENT
One student enrols in many courses.
A course has enrollments from many students.
An enrollment is for a student in a course!
*
Shown by matching values
- First Student row (123-45-6789) related to 1st and 3rd rows of Enrollment table
- First Offering row (1234) related to 1st two rows of Enrollment table
Combine tables using matching values
Relational databases can have many tables (hundreds)
Follow matching values to combine tables:
- Combine Student and Enrollment where StdSSN matches
- Join operation
Relationships
*
*
STUDENT
COURSE
ENROLLMENT
One student enrols in many courses.
A course has enrollments from many students.
An enrollment is for a student in a course!
*
Shown by matching values
- First Student row (123-45-6789) related to 1st and 3rd rows of Enrollment table
- First Offering row (1234) related to 1st two rows of Enrollment table
Combine tables using matching values
Relational databases can have many tables (hundreds)
Follow matching values to combine tables:
- Combine Student and Enrollment where StdSSN matches
- Join operation
Relational Keys
- Candidate Key
Any attribute or combination of attributes that will uniquely identify a tuple in a relation
- Primary Key
A candidate key that is selected to identify tuples uniquely within a relation
- Alternate Keys
Candidate keys that are not selected to be primary key.
- Foreign Key
- Attribute, or set of attributes, within one relation that matches primary key of some (possibly same) relation.
*
*
*
Keys - Example
- Consider the following relation:
EMPLOYEE(empid, name, address, telephone, DOB, department)
Questions:
- Name the candidate keys?
- Name the column or combination of columns best suited to be primary key?
- Does any column look like a potential foreign key?
*
*
CANDIDATE KEYS
- EMPLOYEE(empid, name, address, telephone, DOB, department)
- Empid ?
- Name, address ?
- Name, DOB ?
- Name, address, telephone ?
*
*
PRIMARY KEY
- EMPLOYEE(empid#, name, address, telephone, DOB, department)
- Empid is the most suitable primary key – it is an artificial primary key (to be covered later in the course)
*
*
ALTERNATE KEYS
- EMPLOYEE(empid#, name, address, telephone, DOB, department)
- Name, address
- Name, DOB
- Name, address, telephone
- There are the candidate keys NOT chosen as primary key
*
*
FOREIGN KEY
- EMPLOYEE(empid#, name, address, telephone, DOB, department#)
- “Department#” looks like a potential foreign key!
- What does this mean – what must exist?
*
*
ANOTHER RELATION !
- EMPLOYEE(empid#, name, address, telephone, DOB, department#)
- DEPARTMENT(department#, name, location)
- There must be a primary key in another relation matching to department#.
- Note how the column “department” has a matching column in DEPARTMENT. It means that for a particular department# in EMPLOYEE relation, there will be only ONE matching department# in DEPARTMENT relation.
- It may not be the same name but it must be from the same domain.
*
*
Entity Integrity Rule
- Primary key or any part of a primary key shall NEVER accept NULL values
- NULL means “absence of data”. It does NOT mean zero
- Can be NULL when:
- the value may not exist
- the value may not be known
- the value may be inapplicable
*
*
*
Entity Integrity Rule (cont..)
- An important extension is that all integrity constraints are to (should) be system controlled
- A mechanism should exist to:
- specify the constraint for this rule, and
- enforce it
- Many RDBMSs fail to provide either!
*
*
Therefore…...
*
Referential Integrity Rule
- “The database must not contain any unmatched FOREIGN KEY value”
- C J Date
- An important extension of this is that:
- a foreign key MAY contain NULL values!
- What happens when a primary key is deleted or damaged???
*
*
*
Referential Integrity Rule (cont..)
- Three sub-rules:
- 1. deletion of a primary key CASCADES
- 2. deletion of a primary key NULLIFIES
- 3. deletion of a primary key is RESTRICTED
- The same conditions apply when a primary key is updated
*
*
*
What does database users want?
*
*
- all information easily accessible from one (logical) place
- useful, relevant and up-to-data data
(to allow them to carry out their role) - data that they can trust (integrity)
- good performance from the database
*
*
- control over who gets to see what data
- for the database to be flexible enough to cope with ad-hoc requirements
- ability to change the database structure or business rules quickly and easily (inexpensively)
Everything the ordinary users want AND
What does business managers want?
Use of SQL
*
*
- define database structures
- implement integrity constraints
- insert, delete and modify data
- control access to stored data
- retrieve stored data and produce informative reports
SQL: Interface between database & users
*
*
Database
SQL
Users
How is all this achieved?
1. Data analysis
2. Create Data Model
3. Decide on Candidate relations
4. Normalise relations
5. Implement database structure for specific DBMS
*
*
Using Good Database Design
DBMS Independent
Logical DB Design
DBMS Specific
Physical DB Design
Logical Database Design
- Design data models (ERD)
- Create relations
- Normalise relations
- Why is this called logical design?
*
*
Physical Database Design
- Use specific DBMS software such as Oracle, Microsoft SQL Server, MySQL.
- Create database objects, e.g. Tables, Views, Indexes
- Interact using SQL
- Storage mechanism
*
*
COMMONWEALTH OF AUSTRALIA
Copyright Regulations 1969
WARNING
This material has been copied and communicated to you
by or on behalf of The University of Southern
Queensland pursuant to Part VA of the
Copyright Act
1968 (
the Act
).
The material in thi
s communication may be subject to
copyright under the Act. Any further copying or
communication of this material by you may be the subject
of copyright protection under the Act.
Do not remove this notice.
StudentID LastName Major Level GPA
123-45-6789 WELLS IS Undergrad 6.00
124-56-7890 NORBERT FIN Postgrad 5.70
234-56-7890 KENDALL ACCT Postgrad 6.50
