1 / 12

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

arvin
Download Presentation

What did we cover in the last class?

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


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

  2. Chapter 2: Relational Models

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

  4. 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]

  5. 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)

  6. 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);

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

  8. 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)

  9. 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]

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

More Related