Database homework

Intro to Relational

Databases CS 2215 Introduction to Databases

1

2

What Is a DBMS?

Database: A collection of information.

Eg ?

Examples: Library, University

Database Management System (DBMS) :

software package designed to store

and manage databases.

 Eg: Oracle, SQL server, MySQL, Access

Files vs DBMS : Why bother with

databases ?

Why not just store all the data in a big file

and write C or Java programs to manipulate

the data. 2

3

Why Use a DBMS?

Naïve users sheltered from messy details

Data integrity: Eg: if Bob works in Marketing, make

sure there is a dept. called Marketing.

Reduced application development time: Avoid writing special programs from scratch each time to access data.

Standard Application Interface: increased reliability

3

4

Why Use a DBMS?

Data independence: easier to make changes If how data is stored changes,

don’t have to change views. Forms, etc.

Security: easier to control how data is shared

Concurrent access: allow multiple users to access simultaneously But in a controlled way !

4

5

Different people involved

 DBMS implementers: who build the DBMS like Oracle, MS SQL server

 End users: Use forms & reports, might write SQL queries

 DB application programmers: write programs to make life easier for end users.

 Eg: person who creates forms for library.

 Must know how databases work

 DB administrator (DBA):

 Handles security and authorization

 Crash recovery

 Database tuning as needs evolve

5

6

Overview of course: Relational Model:

Student Database, Fig 1.2

6

STUDENT

Name StudentNumber Class Major

Smith 17 1 CS

Brown 8 2 CS

7

Overview of course:

Data Models: High level : Entity Relation (E.R.) model

Intermediate level : relational model

Student database

Low level: physical database - Covered in CSCI 4524 Advanced Databases

Relational databases: Integrity constraints

Good design : normalization

Query languages: Relational algebra, SQL

Views, Assertions, Triggers 7

8

Relational Data Model

Relation: 2-dimensional table

All info stored in tables

Eg: student, course

See Elmasri Fig 1.2

Rows (or tuples): student : 2 rows

Records: a row may correspond with a record in a file

Commonly used if we are talking about the physical storage of databases

Columns (or attributes): student : 4 columns

8

9

Relational Data Model

Relational model proposed by E. F.

Codd 1970

Dominant model in commercial DBMS

products.

 Eg: Oracle, SQL server, MySQL, Access.

Compared to previous models

(network, hierarchical etc):

Easier to understand info in tables

Casual user can write simple SQL queries

Complex queries much easier to

understand compared to previous models. 9

10

Basic Terminology

 Relational Schema (or head): set of all the column names i.e. what info is being stored. For student table:

Name, StudentNumber, Class, Major

 Relational Database Schema : collection of all the relational schemas

Relational Instance (or current relational state): what data is currently in the table.

 In the Eg in Elmasri Fig 1.2 student database, there are two rows (Smith …, Brown …) in the relational instance.

 Schema vs instance:

schema changes infrequently

 instance changes often as data changes, rows added deleted.

10

11

Relational Rules

Attempt to standardize across different products, so can discuss design issues in general

Have to be true for relation at all times, not just happen to be true for the current relational instance

Some rules violated in some products. Why ? For efficiency and for ease of use, a “clean mathematical design” may be sacrificed.

Some rules violated in SQL itself. 11

12

First Normal Form rule

Every value atomic: No multi-values, composite. Followed by “all” DBMS.

Not followed in OODBMS, ORDBMS

No multi-valued field. Eg: if we are looking at locations of projects, can’t store Los Angeles, NY in single attribute

project location

Finance L.A., N.Y

project location

Finance L.A.

Finance N.Y

12

• How to fix ?

• Split into different

rows

13

First Normal Form rule

No composite values. Eg: if we are looking to store names, can’t have sub- fields of name as fname, lname

13

 How to solve? Split into different columns

name

fname lname

mike smith

fname lname

mike smith

14

Unique Row Rule

 No two rows identical: they have to differ in at least one value (one column).

14

project location

Finance L.A.

Finance L.A.

project location

Finance L.A.

Finance N.Y

OK NOT OK

• SQL allows duplicate rows: default. Why ?

• Lot of work may be needed to ensure no duplicates:

Eg. When combining two tables.

• Duplicates may be useful. Eg. When counting how

many employees make > 50k. Enumerate salaries

and count

15

Rows not ordered

15

project location

Finance L.A.

Sales N.Y

project location

Sales N.Y

Finance L.A.

same as

• To access rows: do by content (where is Sales

located) rather than row # (what is the location

in the 2nd row). We will assume this is true.

• Commercial DBMS break this rule. Why ?

• Rows ordered: logically no, physically yes

• For performance, because of physical

locality, order can matter.

16

Columns not ordered

16

project location

Finance L.A.

Sales N.Y

location project

L.A. Finance

N.Y Sales

same as

• Property of relations, but rule violated in SQL: Eg:

when inserting a new row into the table above left,

can just insert Marketing, Chicago.

– Don’t have to specify project = Marketing, location =

Chicago. How ?

• Since Marketing written before Chicago, DBMS will

assume Marketing goes into 1st column, Chicago

goes into 2nd column.

17

Domain

The values an attribute can take: Eg:

string, integer, real

Domain of each attribute is specified

when creating a table in SQL

Enforced by the DBMS when making

changes or adding a new row.

Eg: age: integer

If try to set age to 7.3, DBMS will not allow

17

18

Null value

 An attribute can take null value. How to interpret ?

 Doesn’t exist: Eg ?

 Supervisor attribute for employees, but some employees may not have a supervisor.

Don’t know the value: Eg ?

 Birth date

 Don’t know if it exists or unknown value: Eg ?

Work phone for customers in online store database: customer may not have, or may choose not to give

18

19

Keys

Super key of a relation: one or more

attributes that uniquely identifies which

rows we are talking about.

Eg: for COURSE table which are super

keys?

{C#}, {CName}, {C#, CName},

{C#,Dept}…

Has to be time invariant: always true, not

just happens to be true for current

instance

Eg: in STUDENT, Name is unique, but may

not always be true 19

20

Keys

 Have to look at semantics: to decide if

superkey, is not enough to look at instance

Key: a super key which is minimal i.e. if we

remove any attribute from a key, will no

longer uniquely identify a row.

Eg: {C#, CName} for COURSE is a super key,

but not a key because C# is a key.

 Composite keys: more than one attribute.

Eg?

 Eg: GRADEREPORT, only key is {St#,SecId}

20

21

Keys

 Can have multiple keys: Eg: C#, Cname keys

 Primary Key: One key is picked as primary key. Shown by underlining.

 Which is picked can have an impact

 Foreign keys can refer only to primary keys

 File may be sorted by primary key (or index), so searches may be more efficient (Covered in CSCI 4524).

 Pick natural and simple one. Eg: C#

 Can also be done through auto-number

 All primary keys for course database ?

21

22

Foreign Keys

 Suppose added a new row in GRADEREPORT : (14,117,B). Is this OK ?

 No: because no 117 in section:

any section is in GRADEREPORT also has to be in SECTION table.

 Section_id in GRADEREPORT is foreign key to Section_id in SECTION :

a value for Section_id in GRADEREPORT has to also occur in SECTION

 How to show: using arrows.

 Foreign key, primary keys are glue for diff. tables

 All foreign keys for course database

22

23

Foreign Keys

 F. Key may be composite:

 Eg: if in student table: (fname, lname) P. Key

 In GRADEREPORT (fname, lname) stored instead of

student_number

 (fname, lname) composite F. Key. Is this different to saying fname F. Key and lname F. Key ?

 F. Key has to be Time invariant: always true

 Not enough that it is true for one instance

 Eg: student_number from Student to GRADEREPORT

Is this a valid F. Key ?

 No : in other instances may not be the case

23

24

Foreign Keys

F. Key can be null:

Eg: suppose had an instructor table also

Section 145 has no instructor

So either F. Key should be null or exist in

referenced table

F. Key could itself be part of P. key: Eg ?

In Prerequisite table, Prerequisite_number

Self referential: F. Key can refer to a P.

Key in the same relation.

Eg: if prerequisite info being kept in Course

table 24

25

Elmasri Company Database

The company is organized into DEPARTMENTS.

Each department has a name, number and an employee who manages the department

We keep track of the start date of the department manager

Departments can have multiple locations

Each department controls a number of PROJECTs.

Each project has a name, number and is located at a single location.

25

26

Elmasri Company Database

 For each EMPLOYEE, we store the social security number, address, salary, sex, and birthdate.

 Employees may have a supervisor

DIFFERENT FROM (and no connection to) manager

 Each employee works for one department but may work on several projects.

 We keep track of the number of hours per week that an employee currently works on each project.

 Each employee may have a number of DEPENDENTs.

For each dependent, we keep track of their name, sex, birthdate, and relationship to employee. 26

27

Elmasri Company Database

What will be the different tables ?

What will be the primary keys ?

What will be the foreign keys ?

An example instance

27

28

Elmasri COMPANY database schema

28

29

Elmasri database with FK: Figure 3.7

29

30

Elmasri

Figure 3.6:

Relational

Instance

30

31

In class problem: relational schemas

31

Database keeps track of student enrollment in courses

and the books adopted for each course:

STUDENT(SSN, Name, Major, Bdate)

COURSE(Course#, Cname, Dept)

ENROLL(SSN, Course#, Quarter, Grade)

BOOK_ADOPTION(Course#, Quarter, Book_ISBN)

TEXT(Book_ISBN, Book_Title, Publisher, Author)

• Why is Quarter part of P.Key in Enroll ?

• Suppose Book_ISBN was part of P.K. in the

BOOK_ADOPTION table

– How would we interpret that ?

• Draw relational schema specifying foreign keys.

32

Premiere Products Database:

from Pratt and Adamski

 Sales Reps

Sales rep number, last name, first name,

address, total commission, commission rate

 Customers

Customer number, name, address, current

balance, credit limit, customer sales rep

 Parts Inventory

Part number, description, number units on

hand, item class, warehouse number, unit

price

32

33

33

Figure 1.2: Premiere Products Sample Order

34

Premiere Products Customer Order

Order

Order number, order date, customer number

Order line

Order number, part number, number units ordered, unit price

Overall order total

Not stored since it can be calculated 34

35

35

Premiere Products Sample Data

36

36

Premiere Products Sample Data

L2 Relational DDL Part 1 LP.pdf