exam 1 review: erds, schemas, sql out describe elements of an erd create schema from erd notes:...

Exam 1 Review:ERDs, Schemas, SQL Out• Describe Elements of an ERD• Create Schema from ERD• Notes: Associative Entities

SectionSection

CourseCourse

HasHas

CRNCRN

SeatsSeats

CourseIDCourseID

TitleTitle

• Describe Elements of this ERD• Convert it into a Schema

LocationLocation

1 table

many table

ER Diagram Elements

• Entities?– Rectangles: Section, Course

• Attributes?– Ovals: CRN, Seats, Location, CourseID, Title

• Relationships?– Diamonds: Course HAS Section

• Cardinality of Relationship? – 1 course has at least one, at most many sections– 1 section has at least one, at most one course

Create the Schema: Rules1. Create a table for every entity

2. Create table fields for every entity’s attributes

3. Implement relationships between the tables

SectionSection

CourseCourse

HasHas

CRNCRN

SeatsSeats

CourseIDCourseID

TitleTitle

Make the Schema

Course

CourseID

Title

Section

CRN

Seats

Location

CourseIDLocationLocation

FacultyFaculty

CourseCourse

TeachesTeaches

FacultyIDFacultyID

First nameFirst name

LastNameLast

Name

Course NumberCourse

Number

Course name

Course name

SemesterSemester

Describe the relationship in this ERD(Notice the many:many Relationship)

Create the Schema: Rules1. Create a table for every entity

2. Create table fields for every entity’s attributes

3. Implement relationships between the tables

FacultyFaculty

CourseCourse

FacultyIDFacultyID

First nameFirst name

LastNameLast

Name

Course NumberCourse

Number

Course name

Course name

SemesterSemester

When Implementing Many:Many Relations in a Schema, Conceptually you are Doing This…

Faculty_CourseFaculty_Course

FacCourseID

FacCourseID

HasHas

HasHas

NOTE: NOT A VALID ERD

Faculty

FacultyID

FirstName

LastName

Course

CourseNumber

CourseName

Faculty-Course

FacultyCourseID

FacultyID

CourseNumber

Semester

FacultyCourseID FacultyID CourseID Semester

1 5 10 Spring 2011

2 5 12 Fall 2011

3 3 10 Spring 2011

4 3 12 Fall 2011

Converting that ERD into a schema

SQL Out• SQL SELECT from 1 or Multiple Tables

• What is a JOIN really?• ORDER BY, GROUP BY, MIN(), MAX()

SELECT from 1 Table

• SELECT field1, field2, … FROM table1 WHERE condition1 AND condition2 AND …– Retrieves field1, field2, etc. from table1 where

condition1, condition2, etc. are met.

• Example: SELECT * FROM faculty_course WHERE FacultyID = 3

FacultyCourseID FacultyID CourseID Semester

3 3 10 Spring 2011

4 3 12 Fall 2011

SELECT from N Tables

• SELECT field1, field2, … FROM table1, table2 WHERE join_condition AND condition1 AND condition2 AND …– Retrieves field1, field2, etc. from table1, table2 where

records are related to one another, and where condition1, condition 2 are met.

• Example: SELECT LastName, Semester FROM faculty, faculty_course WHERE faculty.facultyID = faculty_course.facultyID

What Happens For a JOIN Really?

• SELECT * FROM table1, table2– Fetches every possible combination (pair) of

records from table 1 and table 2, displays all fields

Table 1

Record 1

Record 2

Record 3

Table 2

Record 1

Record 2Results

T1.Record 1 T2.Record 1

T1.Record 1 T2.Record 2

T1.Record 2 T2.Record 1

T1.Record 2 T2.Record 2

T1.Record 3 T2.Record 1

T1.Record 3 T2.Record 2

OwnerID Name Street City State Zip

1 Pete Front St. New York NY 12212

2 Tom Dock St. Philadelphia PA 19122

3 Sandy Bloor St. Washington DC 10009

4 Jim Carlton St. Seattle WA 48323

5 Joe Bathurst St. Boston MA 09134

JOIN with No Conditions

PetID OwnerID Name Weight Type

1 2 Rex 40 Dog

2 2 Snowball 10 Cat

3 4 Goldie 1 Fish

4 1 Lizzy 6 Lizard

SELECT * FROM pet, owner

PetID Name Weight Type OwnerID OwnerID Name Street City State Zip

1 Rex 40 Dog 2 1 Pete Front St. New York NY 12212

1 Rex 40 Dog 2 2 Tom Dock St. Philadelphia PA 19122

1 Rex 40 Dog 2 3 Sandy Bloor St. Washington DC 10009

1 Rex 40 Dog 2 4 Jim Carlton St. Seattle WA 48323

1 Rex 40 Dog 2 5 Joe Bathurst St. Boston MA 09134

2 Snowball 10 Cat 2 1 Pete Front St. New York NY 12212

2 Snowball 10 Cat 2 2 Tom Dock St. Philadelphia PA 19122

2 Snowball 10 Cat 2 3 Sandy Bloor St. Washington DC 10009

2 Snowball 10 Cat 2 4 Jim Carlton St. Seattle WA 48323

2 Snowball 10 Cat 2 5 Joe Bathurst St. Boston MA 09134

3 Goldie 1 Fish 4 1 Pete Front St. New York NY 12212

3 Goldie 1 Fish 4 2 Tom Dock St. Philadelphia PA 19122

3 Goldie 1 Fish 4 3 Sandy Bloor St. Washington DC 10009

3 Goldie 1 Fish 4 4 Jim Carlton St. Seattle WA 48323

3 Goldie 1 Fish 4 5 Joe Bathurst St. Boston MA 09134

4 Lizzy 6 Lizard 1 1 Pete Front St. New York NY 12212

4 Lizzy 6 Lizard 1 2 Tom Dock St. Philadelphia PA 19122

OwnerID Name Street City State Zip

1 Pete Front St. New York NY 12212

2 Tom Dock St. Philadelphia PA 19122

3 Sandy Bloor St. Washington DC 10009

4 Jim Carlton St. Seattle WA 48323

5 Joe Bathurst St. Boston MA 09134

JOIN Conditions in WHERE clause

PetID OwnerID Name Weight Type

1 2 Rex 40 Dog

2 2 Snowball 10 Cat

3 4 Goldie 1 Fish

4 1 Lizzy 6 Lizard

SELECT * FROM pet, owner WHERE pet.ownerID = owner.ownerID

PetID Name Weight Type OwnerID OwnerID Name Street City State Zip

1 Rex 40 Dog 2 1 Pete Front St. New York NY 12212

1 Rex 40 Dog 2 2 Tom Dock St. Philadelphia PA 19122

1 Rex 40 Dog 2 3 Sandy Bloor St. Washington DC 10009

1 Rex 40 Dog 2 4 Jim Carlton St. Seattle WA 48323

1 Rex 40 Dog 2 5 Joe Bathurst St. Boston MA 09134

2 Snowball 10 Cat 2 1 Pete Front St. New York NY 12212

2 Snowball 10 Cat 2 2 Tom Dock St. Philadelphia PA 19122

2 Snowball 10 Cat 2 3 Sandy Bloor St. Washington DC 10009

2 Snowball 10 Cat 2 4 Jim Carlton St. Seattle WA 48323

2 Snowball 10 Cat 2 5 Joe Bathurst St. Boston MA 09134

3 Goldie 1 Fish 4 1 Pete Front St. New York NY 12212

3 Goldie 1 Fish 4 2 Tom Dock St. Philadelphia PA 19122

3 Goldie 1 Fish 4 3 Sandy Bloor St. Washington DC 10009

3 Goldie 1 Fish 4 4 Jim Carlton St. Seattle WA 48323

3 Goldie 1 Fish 4 5 Joe Bathurst St. Boston MA 09134

4 Lizzy 6 Lizard 1 1 Pete Front St. New York NY 12212

4 Lizzy 6 Lizard 1 2 Tom Dock St. Philadelphia PA 19122

PetID Name Weight Type OwnerID OwnerID Name Street City State Zip

1 Rex 40 Dog 2 1 Pete Front St. New York NY 12212

1 Rex 40 Dog 2 2 Tom Dock St. Philadelphia PA 19122

1 Rex 40 Dog 2 3 Sandy Bloor St. Washington DC 10009

1 Rex 40 Dog 2 4 Jim Carlton St. Seattle WA 48323

1 Rex 40 Dog 2 5 Joe Bathurst St. Boston MA 09134

2 Snowball 10 Cat 2 1 Pete Front St. New York NY 12212

2 Snowball 10 Cat 2 2 Tom Dock St. Philadelphia PA 19122

2 Snowball 10 Cat 2 3 Sandy Bloor St. Washington DC 10009

2 Snowball 10 Cat 2 4 Jim Carlton St. Seattle WA 48323

2 Snowball 10 Cat 2 5 Joe Bathurst St. Boston MA 09134

3 Goldie 1 Fish 4 1 Pete Front St. New York NY 12212

3 Goldie 1 Fish 4 2 Tom Dock St. Philadelphia PA 19122

3 Goldie 1 Fish 4 3 Sandy Bloor St. Washington DC 10009

3 Goldie 1 Fish 4 4 Jim Carlton St. Seattle WA 48323

3 Goldie 1 Fish 4 5 Joe Bathurst St. Boston MA 09134

4 Lizzy 6 Lizard 1 1 Pete Front St. New York NY 12212

4 Lizzy 6 Lizard 1 2 Tom Dock St. Philadelphia PA 19122

Order By, Group By

• ORDER BY:– Lists records in ascending or descending order, based on the

specified field– Default is ascending (ASC). Specify DESC to make it

descending.

• GROUP BY:– Return the SELECT results for each categorical value (group)

in the specified field– One result is returned for each group

PetID Name Weight Type OwnerID OwnerID Name Street City State Zip

3 Goldie 1 Fish 4 4 Jim Carlton St. Seattle WA 48323

4 Lizzy 6 Lizard 1 1 Pete Front St. New York NY 12212

2 Snowball 10 Cat 2 2 Tom Dock St. Philadelphia PA 19122

1 Rex 40 Dog 2 2 Tom Dock St. Philadelphia PA 19122

• SELECT * FROM pet, owner WHERE pet.ownerID = owner.ownerID ORDER BY weight ASC– DESC instead of ASC would reverse the order

• SELECT count(*) FROM pet, owner WHERE pet.ownerID = owner.ownerID GROUP BY city

Count(*) City

1 Seattle

1 New York

2 Philadelphia

Min(), Max()

• SELECT min(field1), field2 FROM table – Returns 1 record; the smallest value in column “field1”

and the first value in column “field2”

• SELECT field FROM table ORDER BY field – Returns all values, but the smallest one will appear first– Safer, because “min(field1)” and “field2” result

probably don’t come from the same record!!

• SELECT max(field) FROM table – Works the same way, but gives biggest value