Review: Binary vs. Ternary Relationships name lot dname age ssn • If each policy is Employees Covers Dependents owned by just 1 Policies employee: Bad design – Key constraint policyid cost on Policies would name dname age mean policy can ssn lot only cover 1 Dependents Employees dependent! Purchaser Beneficiary • What are the Better design Policies additional constraints in the 2nd diagram? policyid cost 51
Binary vs. Ternary Relationships (Contd.) • The key constraints allow us to combine CREATE TABLE Policies ( Purchaser with Policies and Beneficiary with policyid INTEGER, Dependents. cost REAL, • Participation ssn CHAR(11) NOT NULL, constraints lead to PRIMARY KEY (policyid). NOT NULL constraints. FOREIGN KEY (ssn) REFERENCES Employees, • What if Policies is a ON DELETE CASCADE); weak entity set? CREATE TABLE Dependents ( PK of Policies: dname CHAR(20), (policyid, ssn) age INTEGER, PK of Dependents: policyid INTEGER, (dname, policyid, ssn) PRIMARY KEY (dname, policyid). FOREIGN KEY (policyid) REFERENCES Policies ON DELETE CASCADE); 52
53
54
An Example CREATE TABLE Student ( ID NUMBER, Fname VARCHAR2(20), Lname VARCHAR2(20), ); 55
Constraints in Create Table • Adding constraints to a table enables the database system to enforce data integrity. • Different types of constraints: * Not Null * Default Values * Unique * Primary Key * Foreign Key * Check Condition 56
Not Null Constraint CREATE TABLE Student ( ID NUMBER, Fname VARCHAR2(20) NOT NULL, Lname VARCHAR2(20) NOT NULL, ); 57
Primary Key Constraint CREATE TABLE Student ( ID NUMBER PRIMARY KEY, Fname VARCHAR2(20) NOT NULL, Lname VARCHAR2(20) NOT NULL, ); •Primary Key implies: * NOT NULL * UNIQUE. •There can only be one primary key. 58
Primary Key Constraint (Syntax 2) CREATE TABLE Students ( ID NUMBER, Fname VARCHAR2(20) NOT NULL, Lname VARCHAR2(20) NOT NULL, PRIMARY KEY(ID) ); Needed when the primary key is made up of two or more attributes (fields) 59
Foreign Key Constraint CREATE TABLE Studies( Course NUMBER, Student NUMBER, FOREIGN KEY (Student) REFERENCES Students(ID) ); NOTE: ID must be unique (or primary key) in Students table 60
Search
