Transcript Document 7566801

Before Starting
•
•
•
•
•
•
•
•
Data Integrity
Data Types
Record (line, tupel)
Field (attribute, column)
Table (file, relation)
Data Dictionary (repository, metadata)
Backup & Recovery
DataWarehouse, DataMart, DataMining
Database Design Guidelines
• What is the purpose of the database?
• Design the tables (ERD on paper)
• Decide which fields to include for each table
(datadictionary)
• Create the relationships between tables
• And then start the implementation
CASE 1
Bogus University hired you to design a
database for them. They would like to keep
track of each student, classes, and rooms so
that they would like to be able to find which
student is taking which classes or which
room is available on a given day, etc.
Assume each class can only be thought by a
single instructor.
Design the Tables
Students
Classes
Rooms
???
Decide which fields to include for
each table
Students
Classes
Student_ID, Student_Name,
Student_Phone, Student_Address
Class_ID, Class_Name, Credits,
Instructor, Prerequisite, Days, Time,
Room_ID
Rooms
???
Room_ID, Building, Number
Create the relationships between
tables
• Determine the relationship types first.
• One-To-One
• One-To-Many
• Many-To-Many
• Resolve Many-To-Many relationship in 2 One-ToMany relationships
• Keep on asking yourself “One … can (have, teach,
purchase, etc.) many …, but one …cannot (have,
teach, purchase, etc.) many …
Create the relationships between
tables (Cont’d)
• Select Primary Keys for each table among
the Candidates and place the Foreign Keys
into tables if necessary.
Tables
Relationships
CASE 2
Sham Bookstore wants you to design a
database to keep records of the books they
have, their authors, and their publishers.
Assume that each book can be published by
a single publisher and the order of authors
does not matter.
Design the Tables
Books
Authors
Publishers
???
Decide which fields to include for
each table
Books
Authors
ISBN, Book_Name, Pages
Author_ID, Author_Name
Publishers
???
Publisher_ID, Pub_Name,
Pub_Phone, Pub_Address
Create the relationships between
tables
• Can one book be published by many
publishers?
• Can one publisher publish many books?
• Can one book have many authors?
• Can one author have many books?
Aha!.
Tables
Relationships
SQL
Structured Query Language
SELECT fieldname1, fieldname2…fieldnameN
FROM table1, table2…tableM
WHERE (Table Joins)
AND (any specifications, eg. students living in Balto City.)
GROUP BY (used if an aggregate function exist in select clause)
HAVING (any specifications about aggregate functions)
ORDER BY fieldname2, fieldname1;
SQL Examples
List all student names and phone #s in
alphabetical order
SELECT Student_Name, Student_Phone
FROM Students
ORDER BY Student_Name;
SQL Examples
List all student names and phone #s of
students whose ID is larger than 233000 in
descending alphabetical order
SELECT Student _ID, Student_Name,
Student_Phone
FROM Students
WHERE Student_ID > 233000
ORDER BY Student_Name;
SQL Examples
List all book names and publisher names
SELECT Book_Name, Pub_Name
FROM Books, Publishers
WHERE Books.Publisher_ID =
Publishers.Publisher_ID;
SQL Examples
List all book names and publisher names of
books smaller than 300 pages.
SELECT Book_Name, Pub_Name
FROM Books, Publishers
WHERE Books.Publisher_ID =
Publishers.Publisher_ID
AND Pages < 300;