Abstract. This tutorial consists of 4 questions of ER diagrams. Q1 is a small exam again any time after a week of the failed exam date, at any branch. If he passes the License: license no, license class, license expiry. Learner 7. Each pharmacy sells several drugs and has a price for each. A drug could. myCourses shell - (Sec class) Database Design Using Entity Relationship Diagrams by Sikha Bagul & Richard Erd [ERD] abstractions when designing & testing software. Week 2 (Sep 3) No Class Java Concurrency Tutorial. Privacy & Terms | View desktop site In this lab, you will draw a relationship diagram for two of the steps shown. This is different than an entity/relationship diagram (ERD) that we will draw next week, where the "many" side has crow's feet. Important: Before starting, be sure to review the Microsoft Visio Tutorial located.
Normalizing with Entity Relationship Diagramming
Figure 5 Multi-valued dependency equivalency in ERD occurs when attributes within an entity instance have more than one value. This is a situation when some attributes within an entity instance have maximum cardinality of N more than 1. When an attribute has multiple values in an entity instance, it can be setup either as a composite key identifier of the entity type, or split into a weak entity type.
For example, consider the following entity type Student Details as shown in Figure 6. The composition of entity identifier is due to the fact that a student has multiple MajorMinor values along with being involved in multiple activities.
The multi-valued dependency affects the key structure. This means that a SID value is associated with multiple values of MajorMinor and Activity attributes, and together they determine other attributes.
The entity instance of Student Details entity type is shown Figure 7. Each normal form rule and its application is outlined. First Normal Form 1NF The first normal form rule is that there should be no nesting or repeating groups in a table. Now an entity type that contains only one value for an attribute in an entity instance ensures the application of first normal form for the entity type. So in a way any entity type with an entity identifier is by default in first normal form.
For example, the entity type Student in Figure 2 is in first normal form. Second Normal Form 2NF The second normal form rule is that the key attributes determine all non-key attributes.
A violation of second normal form occurs when there is a composite key, and part of the key determines some non-key attributes. The second normal form deals with the situation when the entity identifier contains two or more attributes, and the non-key attribute depends on part of the entity identifier.
For example, consider the modified entity type Student as shown in Figure 8. The entity type has a composite entity identifier of SID and City attributes. Figure 8 An entity instance of this entity type is shown in Figure 9.
Now, if there is a functional dependency City? Status, then the entity type structure will violate the second normal form. Figure 9 To resolve the violation of the second normal form a separate entity type City with one-to-many relationship is created as shown in Figure The relationship cardinalities can be further modified to reflect organizational working.
In general, the second normal form violation can be avoided by ensuring that there is only one attribute as an entity identifier. This normal form is violated when there exists a dependency among non-key attributes in the form of a transitive dependency.
For example consider the entity type Student as shown in Figure 4. In this entity type, there is a functional dependency BuildingName? Fee that violates the third normal form. Transitive dependency is resolved by moving the dependency attributes to a new entity type with one-to-many relationship. In the new entity type the determinant of the dependency becomes the entity identifier. The resolution of the third normal form is shown in Figure The Boyce-Codd normal form rule is that every determinant is a candidate key.
Even though Boyce-Codd normal form and third normal form generally produce the same result, Boyce-Codd normal form is a stronger definition than third normal form. Every table in Boyce-Codd normal form is by definition in third normal form.
Boyce-Codd normal form considers two special cases not covered by third normal form: Part of a composite entity identifier determines part of its attribute, and a non entity identifier attribute determines part of an entity identifier attribute. These situations are only possible if there is a composite entity identifier, and dependencies exist from a non-entity identifier attribute to part of the entity identifier.
For example, consider the entity type StudentConcentration as shown in Figure The entity type is in third normal form, but since there is a dependency FacultyName?
MajorMinor, it is not in Boyce-Codd normal form. Figure 12 To ensure that StudentConcentration entity type stays in Boyce-Codd normal form, another entity type Faculty with one-to-many relationship is constructed as shown in Figure Figure 13 Fourth Normal Form 4NF Fourth normal form rule is that there should not be more than one multi-valued dependency in a table.
For example, consider the Student Details entity type shown in Figure 6. Now, during requirements analysis if it is found that the MajorMinor values of a student are independent of the Activity performed by the student, then the entity type structure will violate the fourth normal form.Entity Relationship Diagram (ERD) Training Video
To resolve the violation of the fourth normal form separate weak entity types with identifying relationships are created as shown in Figure The StudentFocus and StudentActivity entity types are weak entity types. It is now presumed that the Student entity type has the functional dependency SID?
Due to the similarity in the notion of an entity type and a relation, normalization concepts when explained or applied to an ERD may generate a richer model. Also, such an application enables a better representation of user working requirements.
This application now results in the specification of additional guidelines for refining an ERD.
Professor Hossein Saiedian: Fundamentals of Databse Engineering and Management
These guidelines can be stated as follows: There should be only one dependency in each entity type where the determinant is the entity identifier. There should not be any additional dependency among the non entity identifier attributes. Any such additional dependency should be represented by a new entity type with one-to-many relationship. If there is a composite entity identifier of three or more attributes it should be ensured that there is only one multi-valued dependency among them.
Study of dependencies among attributes during requirement analysis assist in entity type identifications and cardinality specifications. Since an ERD represents a relational model schema, a normalization ERD improves the modeling effort thereby facilitating a better fit with organizational working. Enhancing the ER model with integrity methods. Journal of Database Management, 10 4Accuracy in modeling with extended entity relationship and object oriented data models.
Journal of Database Management, 4 4 Object-Oriented Analysis and Design with Applications second edition. On the satisfiability of dependency constraints in entity- relationship schemata. Thursday December 14, 6: Students are expected to read assigned articles from the textbook or the reading list.
Business Process Modeling - ppt download
Students are expected to actively participate in classroom discussions, make presentations, and regularly make contributions such as offering comments, asking interesting questions, and responding with good answers.
Suggested Readings The textbook is an excellent survey and tutorial resource. Most up-to-date topics on database design can be found in technical journals and recent conference proceedings.
E-Mail Communication E-mail communication is fast, flexible, and effective. You are expected to have an ku. Important classroom notes will be communicated via email. Do not send email in HTML format; it will not be processed. Unless you are specifically asked to send a document in PDF formatsend text-only emails in text-only format.
Business Process Modeling
See the Guidelines for Submitting Electronic Documents. Video Discs A number of database and software engineering video discs have been prepared for the course. If time allows, some may be shown. Other Policies Students are expected to conduct themselves very professionally, initiate or engage in informative discussions, and avoid activities that could cause a distraction either for other students or for the instructor.
Students are expected to come to the class on time, be attentive and engaged, conduct themselves very professionally, engage in informative discussion, and avoid anything that could cause a distraction or be detrimental to other students learning or for instructor's presentations.
Profanity and swearing is not allowed. If a student will have to take an exam at a later time due to an excused and verified absencehe or she will be asked to make the following pledge and sign it: I pledge that I will not obtain from anyone by any means in writing, speaking or via digital communications any information about the exam. Attendance is important and required. If a student misses a class session, he or she will be entirely responsible for learning the materials missed without the benefit of a private lecture on the instructor's part.
Furthermore, the student will be responsible for finding out what assignments may have been given and when they are due.