Review Questions:
- How to handle multi-valued attributes
- How to handle data on relationships
- Memorize ERD ==> RM Rules
- Course and Sections (why splits), Course and Prerequisites (Two entities or one?), Grade (where to store it), minimum grade required for prerequisites
Lecture 1: SQL
- Programming Languages: non=procedural and 4th generation
- SQL consists of:
- DDL (Data Definition Language) -- used to create, modify, and delete database objects such as tables, views, accounts, indices, triggers, stored procedures and functions, etc.
- DML (Data Manipulation Language)
- DML Create Table Syntax : A SQL DDL command for creating a new table contains a list of columns and constraints (business rules). For each column, specify column name, data type, and field size and optionally specify whether the column is mandatory (not null) or optional (null), without which, the column will be optional by default. For each constraint, specify constraint name, constraint type (primary key, foreign key, or check), and actual constraint content.
create table XYZ (
column_name data_type (size) [Null or Not Null],
...
constraint constraint_name constraint_type (constraint_content),
...
);
Referential Integrity Rule #1: Field type and size for a foreign key column must match the corresponding primary key column.
Note: Foreign key columns can have different names from the corresponding primary key columns.
Domain Validation and Business Rules: check constraints may be used to either enforce data domains (column values must be in a list of allowed ones) or implement business rules.
Example: Write SQL statements to implement the student enrollment database.
- How can you ensure a business policy on the database server that a student grade must be A, B, C, D, or F.
- How do you enforce the business rule that admission date must be after birth date?
Exercise: Write SQL statements to implement student registration data model
Lecture 2: Implement the relational model using Oracle
- How to logon to Oracle Server
- How to create, change, and delete Oracle user accounts
- Database development: create a new table, create fields with data types, and create primary keys and foreign keys with first referential integrity rule
- How to delete tables with or without constraints
- How to list all the tables a user has (meta tables:user_tables)
- how to look up the structure of a particular table
- how to find constraints (meta tables: user_constraints, user_cons_columns)
Exercise:
A store sells raw materials as well as components that are made of other components and raw materials and wants to have a database to keep records of its customers and orders. Here are the specs of their production and sales. 1) A component can be used to make any of several other components; 2) A component can be made of several other components 3) A component can be constructed from several raw materials; 4) A raw material is used in several components; 5) Each component is produced by a single worker; 6) A worker can produce many kinds of components; 7) A component may need a certain quantity of other components and raw materials; 8) There are two kinds of raw materials: perishable ones that have definite expiration time and desirable storage temperature, and non-perishables that does not have expiration time or storage restrictions; 9) a customer may purchase multiple quantities of some components or raw materials and may get volume discount. Submit your ERD, the converted relational model and the SQL code in one file to ecourse.org
- Use Data Modeler to create an ERD to capture the data requirements and business rules. Make up your own attributes for each entity involved.
- Convert the ERD into a relational model
- Write SQL statements to implement your relational model
Lecture 3: DBMS: To actually create a database, we need a software program, called DBMS (Database Management System) that lets you create databases and manipulate data in the database. Tasks of DBMS include data organization, data manipulation, and security enforcement:
- Data Organization: defining the database structure according to a selected database model such as the relational model
- Data manipulation: retrieve data, modify data, insert new data, delete data, report, sort data
- Security and Integrity enforcement:
- Provide for the safety of information despite system crashes and power failures
- Prevent unauthorized access
- Concurrency control
Database Management Issues:
- Data redundancy: too many duplicate data items
- Data inconsistency: The same information about one entity appears differently in different places of the database
- Data integrity: Data stored must follow certain rules. For example, data for a balance must be of numerical value. A balance for checking account cannot be a negative value
- Automicity of Transactions: A transaction must be either complete or not at all
- Concurrency Control: No two users can modify the same data item at the same time
- Security: prevent unauthorized access, disk failures, and disasters such as fire, flood, theft
Lecture 4. DDL for Other Database Objects: Users, Tables, Constraints, Sequences, Views, Synonyms, and Database Links (see Chapter 18 for more examples)
- How to modify a table
- How to delete a table
- How to list all the tables a user has (meta table user_tables)
- How to look up the structure of a particular table
- How to find constraints (meta tables: user_constraints, user_cons_columns)
Example 1: Write SQL DDL statements to implement the relational model for the registration database (homework 3).
How to modify tables without recreate them?
alter table ... add ...
alter table ... drop column ..
alter table .... drop constraint ...
add table ... modify ...
Example 2: Write SQL statements to implement the student enrollment database.
- How can you ensure a business policy on the database server that a student grade must be A, B, C, D, or F.
- How do you enforce the business rule that admission date must be after birth date?
Homework:
- Reading:
- Chapter 18 of LIU
- Design:
- Correctness Questions: online
-
Convert the following ERD into a relational model manually on paper and then write SQL statements to implement it. Submit both relational model nd SQL statements.
-
|
