Announcements

Announcements#

  • Exam #1 on next monday

    • It will cover everything we did in class, including on this week thursday

  • Open book open notes, bring any printed material with you (within reason)

    • You may not use anything electronic, including your phone, ipad, remarkable, computer and whatever other device I forgot.

    • You may not write on your cribsheets during the exam, only your exam papers.

  • Lecture exercise #8 out today, due on wednesday midnight

  • Hw#2 is due thursday at midnight

    • You can use draw.io for draw ER Diagrams

Fourth Normal Form#

Suppose R is in BCNF!

StudentInfo(RIN, Hobby, Major) F={}

Key:RIN Hobby Major
IN BCNF

  • Students can have more than one hobby

  • Students can have more than one major

1 Drawing CSCI 1 Fishing MATH 1 Drawing MATH 1 Fishing CSCI

A relation is not in 4NF if there are multiple (unrelated) multi-valued attributes in it. A relation in BCNF may not be in 4NF.

Database modeling with Entity-Relationship Diagrams (ER Diagrams)#

  • ER is an object-oriented design tool, but we can map it to relational data model

  • ER model is not standard, so we will follow the book’s notation

  • ER Modeling is an iterative process

  • Entities: basis classes of objects

  • Relationships: connections between different entities

Entities#

Classes of objects

Each entity has at least one key (a number of attributes that are unique to an instance of the entity) and many attributes

Each attribute must be single/simple value

intro

Students: RIN, FirstName, LastName, Email, Address, DoB, Class (Multivalued: Major)

Courses: CrsCode, Name, Subject, Credits (Multivalued: CrossCodes, Prereqs)

Classes: CRN, Semester, Year, MaxCapacity, Classroom (Multivalued: Instructors)

Faculty:
RIN, FirstName, LastName, Email, Address, DoB

studentdb

Movies: id, title, releasedate, length
(multivalued: Genre)
Actors: id, name, screenname, dob, dod, bio
OtherCast:id, name, dob, bio
Franchise: name
Studios: name, address
Awards: id, type, name, frequency

moviedb

Relationships#

  • Combine two or more entities, but mostly two entities (binary relationships)

  • Consider each one with a sentence:

Ex:

  • Students take classes

  • Each class is for a specific course

  • Faculty teach classes

  • Faculty advise students

Only entities participate in relationships
Relationships only connect to entities

moviedb

moviedb

Mapping an ER Diagram to the relational data model#

  • Map all entities to a relation, the key of the entity becomes the key of the relation

  • Relationships:

    • One to many: store it as an attribute on the many side by including the key for the entity on the one side in the other entity

    • One to one: same as one to one, but can do in either direction

    • Many to many: map to a new relation, take the keys of each of the connecting entities and the combination of the keys is the key of the new relation

Student(RIN, FirstName, LastName, Email, Address, DoB, Class)
Courses(CrsCode, Name, Subject, Credits)
Classes(CRN, Semester, Year, MaxCapacity, Classroom, CrsCode, CrossListedMainCRN)
Faculty(RIN, FirstName, LastName, Email, Address, DoB)
StudentsTakeClasses(RIN, CRN)
FacultyTeachClasses(RIN, CRN)

Contents