Transcript Database Management Systems II

Database Management Systems II
Session 5
1
Objectives
1. Design database schema
2. Understand elements of RDBMS
3. Understand use of SQL and read simple
SQL commands
4. Complete a RDBMS exercise using MS
Access
2
Topics
1. Database Schema
2. Queries, Forms and Reports
3. Introduction to SQL
4. Hands-on exercise
3
Scenario
• Draw an E-R diagram:
• Northwind is a wholesaler of delicatessen
products. Customers are all over the world and
each member of the sales team may deal with
any customer. Products is kept in a central
warehouse and items are ordered from
Suppliers when numbers reach the re-order
level. An Order may consist of several different
products and may be delivered by any one of
three Shippers.
4
Entity-Relationship Diagram
5
Table Definition
Table: Contacts
Field Name
Data Type
Size
Indexing
Contact ID
Number
Long Integer
Primary Key
Name
Text
30
Birthday
Date
Birthday Card
Yes/No
6
RDBMS Objects
FORMS
TABLES
QUERIES
REPORTS
7
Forms
8
Reports
9
SQL Code
SELECT ProductID, ProductName
FROM Products
WHERE Discontinued = ‘No’
ORDER BY ProductName;
- What will the query output look like?
- What is this query doing?
10
Exercise
• Honest Pete’s Car Sales is a business that
deals in second hand cars. You have been
given a table of current stock and a table
of sales staff.
• Construct a schema with table definitions
showing primary keys and indicating how
tables should be linked.
• Following your design, construct the
DBMS in MS Access
11