intro

Comp451Database Design: 

Comp451 Database Design Goals: To understand the inner workings of modern Database Management Systems. To be able to install DBMS’s, set up applications, and monitor them for effiency To become a DBA (Database Administrator)

Course format: 

Course format Lectures: Alanoly Andrews, Wednesdays 17:45—20:15 Textbook: Either 'Database System Implementation,' by H. Garcia-Molina, J. D. Ullman, and J. Widom, Prentice Hall, 1999, or 'Database Systems: The Complete Book,' Same authors, Prentic Hall, 2002, Chapters 11-19. Recommended reading: 'Database Tuning,' by D. Sasha and P. Bonnet, Elsevier 2003

Slide3: 

Your task is to form groups of 4 students. Each group will be given exclusive access to a Linux PC with two hard-disks The group will install Oracle 9i on their machine, and run various experiments and benchmarks on it. Lab Component:

Grading Scheme: 

Grading Scheme Assignments 10% Project 25% Midterm 15% Final exam 50%

Topics of the Course:: 

Topics of the Course: Data storage and data layout Index structures Multidimensional index structures Query parsing and optimization Query execution Recovery Concurrency control

Prerequisite knowledge: 

Prerequisite knowledge Comp 353 or equivalent, e.g., Relational data model/ Relational databases Relational algebra SQL Basic course in algorithms and data structures, e.g., Search trees Sorting algorithms Hashing Big-O notation Basic algorithm analysis

The Relational Modelby Ted Codd, 1923 -- 2003: 

The Relational Model by Ted Codd, 1923 -- 2003 The model behind most implementations of databases. Data is always represented as relations (2-dim. tables). SQL is a query language for relational databases and is based on relational algebra. Some basic concepts: Relation Attribute Schema Tuple Key How many of these do you recognize? How many of these are you able to define now?

Relations: 

Relations The way to represent data is through relations. A relation is a two-dimensional table. The order of rows and columns can be exchanged, and it is still the same relation. Example: Relation Attribute Schema Tuple Key

Attribute: 

Attribute An attribute is the name of a column in a relation. It usually describes the meaning of the content in the column.

Schema: 

Schema A schema is a description of a class of relations. It consists of the name of the relation and the set of attributes in the relation. Schema for the above relation Movies(title, year, length, filmtype) A set of schemas for relations are called a database schema

Tuples: 

Tuples A tuple is a row in a table. The values in the row are called components. A relation can be seen as a set of tuples.

Relational Algebra: 

Relational Algebra Relational algebra is a formal language for expressing queries in relational databases Operators in Relational Algebra: Set operations (union, intersection, difference) selection and projection Join Bags (multisets). Why they are used and what the consequence is. More operations, e.g., duplicate removal, grouping How many of the above red words do you recognize? How many of them are you able to define now?

Set Operators: 

Set Operators

Projection: 

Projection

Selection: 

Selection

Natural join: 

Natural join

Join Example: 

Join Example

Bags (multisets): 

Bags (multisets)

Operations on bags: 

Operations on bags

More operations: 

More operations

SQL: 

SQL

SQL general form(select-from-where statements): 

SQL general form (select-from-where statements) SELECT andlt;attribute listandgt; FROM andlt;relation listandgt; WHERE andlt;conditionandgt; GROUP BY andlt;attribute listandgt; HAVING andlt;conditionandgt; ORDER BY andlt;attribute listandgt; Take all possible combinations of tuples from the rels in rel list Throw away tuples that don’t meet the WHERE condition Group remaining tuples according to their values in the attr list in the GROUP BY clause Test each group for the HAVING condition and throw away groups that fail Compute the aggregations mentioned in the SELECT clause Sort the result according the attribute values in the attr list in the HAVING clause.

SQL examples: 

SQL examples Schema Movie(title, year, length, studioName) MovieStar(name, address, gender, birthdate) StarsIn(title, year, starName) Studio(name, address) 'find the stars of Paramount' SELECT starName, Movie.title FROM Movie, StarsIn WHERE Movie.title = StarsIn.title AND Movie.year = StarsIn.Year AND StudioName = ‘Paramount’;

Slide24: 

'find the earliest years of stars appearing in at least three movies' SELECT starName, MIN(year) AS minYear FROM StarsIn GROUP BY starName HAVING COUNT(*) andgt;= 3 ORDER BY minYear Schema Movie(title, year, length, studioName) MovieStar(name, address, gender, birthdate) StarsIn(title, year, starName) Studio(name, address)

Slide25: 

Schema Movie(title, year, length, studioName) MovieStar(name, address, gender, birthdate) StarsIn(title, year, starName) Studio(name, address) 'find the movies not made in Hollywood' SELECT title, year FROM Movie WHERE studioName IN (SELECT name FROM Studio WHERE address NOT LIKE ‘%Hollywood%’ ) Nested SQL queries

SQL Data Definition (DDL): 

SQL Data Definition (DDL) CREATE TABLE MovieStar ( name CHAR(30) address VARCHAR(255) gender CHAR(1) birthdate DATE );

Summary: 

Summary Important concepts to remember: the relational data model relational algebra SQL