is 325 notes for wednesday september 18, 2013. homework grades/feedback i’m behind on homework.. i...
TRANSCRIPT
![Page 1: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/1.jpg)
IS 325
Notes for
Wednesday
September 18, 2013
![Page 2: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/2.jpg)
Homework Grades/Feedback• I’m behind on homework.. I will catch-up this weekend
![Page 3: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/3.jpg)
Today’s Class• Periodically you might need to be reminded why you are here
Why It's Important to Know Computer Programming
The World is Flat
![Page 4: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/4.jpg)
MAPPING UNARY RELATIONSHIPS• Mapping unary relationships
Unary relationships in ER diagrams are mapped in the same way as binary relationships
![Page 5: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/5.jpg)
MAPPING UNARY RELATIONSHIPS• Mapping 1:M unary relationships
The relation mapped from an entity involved in a 1:M unary relationship contains a foreign key that corresponds to its own primary key
![Page 6: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/6.jpg)
MAPPING UNARY RELATIONSHIPS
Mapping a 1:M unary relationship
Sample data records for the mapped relation
Client can be referred by only one client but can refer multiple clients
![Page 7: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/7.jpg)
MAPPING UNARY RELATIONSHIPS• Mapping M:N unary relationships
In addition to the relation representing the entity involved in a unary M:N relationship, another relation is created to represent the M:N relationship itself
This new relation has two foreign keys, both of them corresponding to the primary key of the relation representing the entity involved in the unary M:N relationship
Each of the foreign keys is used as a part of the composite primary key of the new relation
![Page 8: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/8.jpg)
MAPPING UNARY RELATIONSHIPSMapping a M:N unary relationship
Sample data records for the mapped relations
![Page 9: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/9.jpg)
MAPPING UNARY RELATIONSHIPS• Mapping 1:1 unary relationships
Mapped in the same way as 1:M unary relationships
![Page 10: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/10.jpg)
MAPPING UNARY RELATIONSHIPSMapping a 1:1 unary relationship
Sample data records for the mapped relation
![Page 11: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/11.jpg)
MAPPING MULTIPLE RELATIONSHIPS BETWEEN THE SAME ENTITIES
• Mapping multiple relationships between the same entities
Each relationship is mapped
![Page 12: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/12.jpg)
MAPPING MULTIPLE RELATIONSHIPS BETWEEN THE SAME ENTITIES
Mapping multiple relationships between the same entities
Sample data records for the mapped relations
![Page 13: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/13.jpg)
MAPPING WEAK ENTITIES• Mapping weak entities
Weak entities are mapped in a same way as regular entities with one addition:
The resulting relation has a composite primary key that is composed of the partial identifier and the foreign key corresponding to the primary key of the owner entity
![Page 14: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/14.jpg)
MAPPING WEAK ENTITIESMapping a weak entity
Sample data records for the mapped relations
![Page 15: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/15.jpg)
MAPPING WEAK ENTITIESMapping a weak entity with two owners
Sample data records for the mapped relations
![Page 16: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/16.jpg)
MAPPING WEAK ENTITIESMapping a weak entity with no partial identifier
Sample data records for the mapped relations
![Page 17: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/17.jpg)
Example ER diagram : HAFH Realty Company Property Management Database
![Page 18: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/18.jpg)
Example mapped relational schema: HAFH Realty Company Property Management Database
![Page 19: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/19.jpg)
Example: Sample data records for the HAFH Realty Company Property Management Database (part 1)
![Page 20: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/20.jpg)
Example: Sample data records for the HAFH Realty Company Property Management Database (part 2)
![Page 21: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/21.jpg)
RELATIONAL DATABASE CONSTRAINTS
• Relational database constraints - rules that a relational database has to satisfy in order to be valid
Implicit constraints
The implicit relational database model rules that a relational database must satisfy in order to be valid
User-defined constraints
Database constraints that are added by the database designer
![Page 22: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/22.jpg)
RELATIONAL DATABASE CONSTRAINTS
• Implicit constraints
Each relation in a relational schema must have a different name
Each relation must satisfy the following conditions:
Each column must have a different name
Each row must be unique
In each row, each value in each column must be single valued
Domain constraint - all values in each column must be from the same predefined domain
The order of columns is irrelevant
The order of rows is irrelevant
![Page 23: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/23.jpg)
RELATIONAL DATABASE CONSTRAINTS
• More Implicit constraints
Primary key constraint - each relation must have a primary key, which is a column (or a set of columns) whose value is unique for each row
Entity integrity constraint
Referential integrity constraint
![Page 24: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/24.jpg)
RELATIONAL DATABASE CONSTRAINTS
• User-defined constraints
Added by the database designers
![Page 25: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/25.jpg)
RELATIONAL DATABASE CONSTRAINTS
Specific minimum and maximum cardinalities
Sample data records for the mapped relations
![Page 26: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/26.jpg)
RELATIONAL DATABASE CONSTRAINTS
• Business rules
User defined constraints that specify restrictions on databases that are not a part of the standard notation for creating ER diagrams
![Page 27: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/27.jpg)
RELATIONAL DATABASE CONSTRAINTS
Business rule for salary amounts
Sample data records for the mapped relation
![Page 28: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/28.jpg)
RELATIONAL DATABASE CONSTRAINTS
Business rule for the dates of enrollment and graduation
Sample data records for the mapped relation
![Page 29: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/29.jpg)
RELATIONAL DATABASE CONSTRAINTS
Business rule for gender of students in an organization
Sample data records for the mapped relation
![Page 30: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/30.jpg)
MAPPING ASSOCIATIVE ENTITIES• Mapping associative entities
Associative entities are mapped into relational database constructs in the identical way as M:N relationships
![Page 31: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/31.jpg)
Example: An M:N relationship and associative entity mapped into a relation in the same way
![Page 32: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/32.jpg)
MAPPING TERNARY RELATIONSHIPS
• Mapping ternary relationships
Ternary relationships are used as many-to-many-to-many relationships
A new relation is created with foreign keys from the participating entities forming a composite primary key of the new relation
![Page 33: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/33.jpg)
Example: Mapping a ternary relationship
![Page 34: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/34.jpg)
Example: Sample data records for the mapped relations
![Page 35: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/35.jpg)
DESIGNER-CREATED PRIMARY KEYS AND THE AUTONUMBER OPTION
• Autonumber data type option - enables automatic generation of consecutive numeric data values in a column
• Designer-created primary key - primary key column, not called for by the original requirements, added to a table by the database designer
Often used in conjunction with the autonumber data type option
![Page 36: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/36.jpg)
DESIGNER-CREATED PRIMARY KEYS AND THE AUTONUMBER OPTION
Entity and the resulting relation
Sample data records for the relation with a designer-created primary key
Entity and the resulting relation with a designer-created primary key column
![Page 37: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/37.jpg)
ER AND RELATIONAL MODELING• Process of requirements collection should be accompanied by
the ER modeling and then followed by mapping the ER model into a subsequent relational schema
• Some practitioners prefer to create relational schemas straight from the requirements
In such cases, the ER modeling phase is simply omitted
![Page 38: IS 325 Notes for Wednesday September 18, 2013. Homework Grades/Feedback I’m behind on homework.. I will catch-up this weekend](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e2e5503460f94b1e93e/html5/thumbnails/38.jpg)
ER AND RELATIONAL MODELING
• Create relational schemas straight from the requirements is not advisable for following reasons
ER modeling is more suited for visualization of the requirements
Certain concepts can be visualized graphically only in ER diagrams
Every attribute is mentioned only once in the ER diagram
An ER model is a better communication and documentation device