What did we cover in the last class?

What did we cover in the last class? Can’t escape databases at this point in time! DBMS versus databases Databases versus File Systems Transactions, query processing ACID properties Data Models

Chapter 2: Relational Models

Models - again What is a model? Relational model Other ways: Hierarchies Networks Objects (object oriented, XML) Entities and their relationships (E-R). Entities, relationships, constraints. E-R Model  Relational Model

Relational Model Terminology Relations Tuples Attributes Domains Schema Instances Keys Order independence: tuples, attributes Data Types: CHAR, VARCHAR, …., DATE, TIME, etc…. Logical level – [not a physical data structure]

Example Schema Product(maker, model, type) PC(model, speed, ram, hd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) Classes(class, type, country, numGuns, bore, displacement) Ships(name, class, launched) Battles(name, data) Outcomes(ship, battle, result)

SQL: Data Definition An SQL relation is defined using thecreate tablecommand: create table r (A1D1, A2D2, ..., An Dn,(integrity-constraint1), ..., (integrity-constraintk)) r is the name of the relation each Ai is an attribute name in the schema of relation r Di is the data type of values in the domain of attribute Ai Example: create tableInstructor (IDchar(5),name varchar(20) not null,dept_name varchar(20),salarynumeric(8,2)) insert into instructor values (‘10211’, ’Smith’, ’Biology’, 66000); insert into instructor values (‘10211’, null, ’Biology’, 66000);

Integrity Constraints • not null • primary key (A1, ..., An ) • foreign key (Am, ..., An ) references r Example: Declare ID as the primary key for instructor create tableInstructor (IDchar(5),name varchar(20) not null,dept_name varchar(20),salarynumeric(8,2),primary key (ID)) primary key declaration on an attribute automatically ensures not null

Integrity Constraints create tableDepartment( dept_name varchar(20),num_instructors integer, num_students integer,primary key (dept_name)) create tableInstructor (IDchar(5),name varchar(20) not null,dept_name varchar(20),salarynumeric(8,2),primary key (ID),foreign key (dept_name) references department)

Relational Model Drop Relation drop R (R: relation name) drop department Alter Relation alter relation r add A D A: attribute D: domain alter departmentadd address varchar(30) alter relation r drop A alter departmentdrop address Schema, Instances Keys Order independence: tuples, attributes Data Types: CHAR, VARCHAR, …., DATE, TIME, etc…. Logical level – [not a physical data structure]

Next: Relational Algebra Select Project Union Intersection Subtraction Other operators.

What did we cover in the last class?