bsys 2060 lecture 4, april 12 th
DESCRIPTION
BSYS 2060 Lecture 4, April 12 th. Midterm overview Normalization. Agenda. External events Announcements Midterm format Course questions Normalization Bank database exercise Team project. http://www.vef.org/. http://www.newventuresbc.com/the-competition/about-the-competition/. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/1.jpg)
BSYS 2060Lecture 4, April 12th
Midterm overviewNormalization
![Page 2: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/2.jpg)
Agenda
• External events• Announcements• Midterm format• Course questions• Normalization• Bank database exercise• Team project
![Page 4: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/4.jpg)
http://www.newventuresbc.com/the-competition/about-the-competition/
![Page 5: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/5.jpg)
Announcements
• If you miss labs because of Easter stat holidays, you need to do the lab exercise outside of regular lab time
• If you miss your Friday lab because of Open House, you can go to one of the other lab slots
![Page 6: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/6.jpg)
Midterm Format: Free range – open book, open Internet… the only un-natural part is you can’t collaborate.
Image source: “truly free range chickens” by Brookford Farm, CC BY 2.0
![Page 7: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/7.jpg)
50% - Multiple Choice, 25 questions worth 2 each50% - Data Modeling question, 1 page
Held during your Week 5 lab
![Page 8: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/8.jpg)
You are writing the questions…
• Each student in their Week 4 lab needs to come up with a multiple choice question
• Question topics assigned by set• Use local context where applicable in your
questions• Use images where applicable• Sample question process
* At least some of them
*
![Page 9: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/9.jpg)
https://zenportfolios.ca/groups/bsys-2060-2012/forum/
![Page 10: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/10.jpg)
https://zenportfolios.ca/bcit-bsys-2060-2012/midterm-question-submission/
![Page 11: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/11.jpg)
Source: http://www.flickr.com/photos/caius/2300154566/
Normalization…
![Page 12: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/12.jpg)
In relational database design, the process of organizing data to minimize redundancy.
Normalization defined:
Source: http://www.webopedia.com/TERM/N/normalization.html
![Page 13: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/13.jpg)
Normalization usually involves dividing a database into two or more tables and defining relationships between the tables.
Why bother?
Source: http://www.webopedia.com/TERM/N/normalization.html
![Page 14: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/14.jpg)
The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
Why bother?
Source: http://www.webopedia.com/TERM/N/normalization.html
![Page 15: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/15.jpg)
1. Atomicity & uniqueness… No repeating elements or groups of elements and each row of data must have a unique identifier (i.e. primary key)
First normal form
![Page 16: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/16.jpg)
2. No partial dependencies on a concatenated key
Second normal form
![Page 17: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/17.jpg)
3. No dependencies on non-key attributes
Third normal form
![Page 18: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/18.jpg)
An example… orders
![Page 19: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/19.jpg)
1. Atomicity & uniqueness… No repeating elements or groups of elements and each row of data must have a unique identifier (i.e. primary key)
First normal form
![Page 20: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/20.jpg)
So far, it doesn’t meet criteria for 1NF
![Page 21: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/21.jpg)
![Page 22: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/22.jpg)
2. No partial dependencies on a concatenated key
Second normal form
![Page 23: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/23.jpg)
![Page 24: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/24.jpg)
![Page 25: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/25.jpg)
Summary
![Page 26: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/26.jpg)
![Page 27: BSYS 2060 Lecture 4, April 12 th](https://reader035.vdocuments.us/reader035/viewer/2022081723/5681637f550346895dd4610c/html5/thumbnails/27.jpg)