Before Starting

1
Before Starting

• Data Integrity
• Data Types
• Record (line, tupel)
• Field (attribute, column)
• Table (file, relation)
• Data Dictionary (repository, metadata)
• Backup Recovery
• DataWarehouse, DataMart, DataMining

2
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

3
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.

4
Design the Tables
Students Classes
Rooms ???
5
Decide which fields to include for each table
StudentsStudent_ID, Student_Name, Student_Phone, Student_Address ClassesClass_ID, Class_Name, Credits, Instructor, Prerequisite, Days, Time, Room_ID
Rooms Room_ID, Building, Number ???
6
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-To-Many relationships
• Keep on asking yourself One can (have, teach,
  purchase, etc.) many , but one cannot (have,
  teach, purchase, etc.) many

7
Create the relationships between tables (Contd)

• Select Primary Keys for each table among the
  Candidates and place the Foreign Keys into tables
  if necessary.

8
Tables
9
Relationships
10
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.

11
Design the Tables
Books Authors
Publishers ???
12
Decide which fields to include for each table
BooksISBN, Book_Name, Pages AuthorsAuthor_ID, Author_Name
PublishersPublisher_ID, Pub_Name, Pub_Phone, Pub_Address ???
13
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!.

14
Tables
15
Relationships
16
SQLStructured Query Language
SELECT fieldname1, fieldname2fieldnameN FROM
table1, table2tableM 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
17
SQL Examples

• List all student names and phone s in
  alphabetical order

SELECT Student_Name, Student_Phone FROM
Students ORDER BY Student_Name
18
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 gt
233000 ORDER BY Student_Name
19
SQL Examples

• List all book names and publisher names

SELECT Book_Name, Pub_Name FROM Books,
Publishers WHERE Books.Publisher_ID
Publishers.Publisher_ID
20
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 lt 300