{
"cells": [
{
"cell_type": "markdown",
"id": "965e15a6",
"metadata": {},
"source": [
"## Announcements\n",
"\n",
"- Exam #1 on next monday\n",
" - It will cover everything we did in class, including on this week thursday \n",
"\n",
"- Open book open notes, bring any printed material with you (within reason)\n",
" - You may not use anything electronic, including your phone, ipad, remarkable, computer and whatever other device I forgot.\n",
" - You may not write on your cribsheets during the exam, only your exam papers.\n",
" \n",
"- Lecture exercise #8 out today, due on wednesday midnight\n",
"- Hw#2 is due thursday at midnight\n",
" - You can use draw.io for draw ER Diagrams\n"
]
},
{
"cell_type": "markdown",
"id": "5b208546",
"metadata": {},
"source": [
"## Fourth Normal Form\n",
"\n",
"\n",
"Suppose R is in BCNF!\n",
"\n",
"StudentInfo(RIN, Hobby, Major) F={} \n",
"\n",
"Key:RIN Hobby Major \n",
"IN BCNF \n",
"\n",
"- Students can have more than one hobby\n",
"- Students can have more than one major\n",
"\n",
"\n",
"1 Drawing CSCI\n",
"1 Fishing MATH\n",
"1 Drawing MATH\n",
"1 Fishing CSCI\n",
"\n",
"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.\n"
]
},
{
"cell_type": "markdown",
"id": "7d0c2fd8",
"metadata": {},
"source": [
"## Database modeling with Entity-Relationship Diagrams (ER Diagrams)\n",
"\n",
"- ER is an object-oriented design tool, but we can map it to relational data model\n",
"- ER model is not standard, so we will follow the book's notation\n",
"- ER Modeling is an iterative process\n",
"\n",
"- Entities: basis classes of objects\n",
"- Relationships: connections between different entities\n",
"\n",
"### Entities\n",
"\n",
"Classes of objects\n",
"\n",
"Each entity has at least one key (a number of attributes that are unique to an instance of the entity) and many attributes\n",
"\n",
"Each attribute must be single/simple value\n",
"\n",
"\n"
]
},
{
"cell_type": "markdown",
"id": "f55fe776",
"metadata": {},
"source": [
"\n",
"\n",
"Students:\n",
" RIN, FirstName, LastName, Email, Address, DoB, Class\n",
" (Multivalued: Major)\n",
" \n",
"Courses: CrsCode, Name, Subject, Credits\n",
" (Multivalued: CrossCodes, Prereqs) \n",
"\n",
"Classes: CRN, Semester, Year, MaxCapacity, Classroom\n",
" (Multivalued: Instructors) \n",
" \n",
"Faculty: \n",
" RIN, FirstName, LastName, Email, Address, DoB \n",
" \n",
"\n",
"\n"
]
},
{
"cell_type": "markdown",
"id": "57dfe8f7",
"metadata": {},
"source": [
"Movies: id, title, releasedate, length \n",
" (multivalued: Genre) \n",
"Actors: id, name, screenname, dob, dod, bio \n",
"OtherCast:id, name, dob, bio \n",
"Franchise: name \n",
"Studios: name, address \n",
"Awards: id, type, name, frequency \n",
" \n",
"\n"
]
},
{
"cell_type": "markdown",
"id": "f7e3f22d",
"metadata": {},
"source": [
"### Relationships\n",
"\n",
"- Combine two or more entities, but **mostly** two entities (binary relationships)\n",
"- Consider each one with a sentence:\n",
"\n",
"Ex:\n",
"\n",
"- Students take classes\n",
"- Each class is for a specific course\n",
"- Faculty teach classes\n",
"- Faculty advise students\n",
"\n",
"\n",
"Only entities participate in relationships \n",
"Relationships only connect to entities \n",
"\n",
"\n",
"\n",
"\n",
"\n"
]
},
{
"cell_type": "markdown",
"id": "fa645459",
"metadata": {},
"source": [
"### Mapping an ER Diagram to the relational data model\n",
"\n",
"- Map all entities to a relation, the key of the entity becomes the key of the relation\n",
"\n",
"- Relationships:\n",
"\n",
" - 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\n",
" - One to one: same as one to one, but can do in either direction\n",
" - 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\n",
" \n",
" \n",
"Student(RIN, FirstName, LastName, Email, Address, DoB, Class) \n",
"Courses(CrsCode, Name, Subject, Credits) \n",
"Classes(CRN, Semester, Year, MaxCapacity, Classroom, CrsCode, CrossListedMainCRN) \n",
"Faculty(RIN, FirstName, LastName, Email, Address, DoB) \n",
"StudentsTakeClasses(RIN, CRN) \n",
"FacultyTeachClasses(RIN, CRN) "
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "386ed774",
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.13.5"
}
},
"nbformat": 4,
"nbformat_minor": 5
}