Intro to MIS – MGS351 Relational Database DesignDatabase Design OverviewRelational DatabasesSlide 4Design PrinciplesEntity Relationship DiagramsSlide 7Entity - Relationship DiagramsSlide 9Slide 10One-to-One Relationship (1:1)One-to-Many Relationship (1:N)Many-to-Many Relationship (N:M)Multiple RelationshipsSlide 15ERD RemindersTransforming ERD to TablesSlide 18Transforming ERD to Tables – N:M Detailed StepsTransforming ERD to Tables – 1:1Transforming ERD to Tables – 1:NTransforming ERD to Tables – N:M (Before)Transforming ERD to Tables – N:M (After)Slide 24Slide 25Slide 26Slide 27Slide 28Determining RelationshipsSlide 30Slide 31Slide 32Database NormalizationUn-normalized ExampleNormalized ExampleDatabase Normalization GoalDatabase Normalization StepsUn-normalizedFirst Normal Form (1NF)Second Normal Form (2NF)Third Normal Form (3NF)Intro to MIS – MGS351Relational Database DesignExtended Learning Module CDatabase Design OverviewRelational Database DesignEntity Relationship DiagramsOne-to-One (1:1)One-to-Many (1:N)Many-to-Many (N:M)Database Normalization–First, second and third normal formRelational DatabasesStore data in the form of related tables.Relational design is based on database normalization and can be accomplished with ERD’s.Relational DatabasesBenefitsEnsures data integrity and referential integrity–Adding, deleting, updating recordsPowerful to useDesign PrinciplesYou must become a business expertPoor relational database design leads to system failureProper design accurately reflects the organization’s business rulesRelational database design is both an art and scienceEntity Relationship DiagramsDiagramming tool used to design a relational database – analogous to a “blueprint” for databases. Used by designers to:–Organize relational design on “paper”–Communicate design to end users and business experts for verificationEntity Relationship DiagramsIf designed properly, an ERD will accurately reflect the business rules of the organization. It will become a visual illustration of the organization’s business rules. Therefore, a database is just a reflection of these business rules.Entity - Relationship DiagramsEntity Relationship DiagramsEntity - Something about which data is keptAttribute - Descriptive value associated with an entityKey Attribute - Attribute(s) that uniquely identify an entityCustomer entity has name, address, fax number and phone number attributesEntity Relationship DiagramsEntities correspond to database tablesAttributes correspond to database fields in a tableKey attributes correspond to primary keysOne-to-One Relationship (1:1)Student ID Card1 1IssuedJoeSallyTomJean1234-12342323-98682452-82911833-9273One-to-Many Relationship (1:N)Student Library Book1 NBorrowsJoeSallyTomBook ABook BBook CBook DBook EMany-to-Many Relationship (N:M)Student CourseN MEnrollsJoeSallyTomJeanMGS 351MGA 201MGQ 301MGF 301MGB 301Multiple RelationshipsFacultyStudentNMCourseN1Teaches EnrollsMultiple RelationshipsMurrayBootMGQ302MGS351MGF301MGB301RitaColleenSeanJodyWaltMikeJordanAndrewLarryJoeBarbERD RemindersConsider how time changes the relationship between entities.Clearly define entities.Identify all exceptions to the rule…often difficult to find even if you’re the business expert.Transforming ERD to TablesTransform entities into tablesTransform attributes into fieldsTransform the key attribute into the primary key for the tableTransforming ERD to TablesApplying these rules ensures all relationships simplify to 1:N1:1 - Relationships merge into one table. One entity becomes an attribute of the other1:N - Add primary key from the “one” entity as a foreign key in the “many” entity N:M - Add intersection table which includes primary keys from both tablesTransforming ERD to Tables – N:M Detailed Steps1. Add intersection table2. Flip relationship lines around so “many” side faces the intersection table3. Add primary keys from original tables as fields in the intersection table4. Assign a name the intersection table5. Add other relevant fields to intersection table if necessaryTransforming ERD to Tables – 1:1Student ID Card1 1IssuedStudentID CardMerge relationships into one table. One entity becomes an attribute of the other.Transforming ERD to Tables – 1:NStudent Library Book1 NBorrowsPerson #Person #1:N – Add primary key from the “one” entity as a foreign key in the “many” entityPrimary Key Foreign Key Transforming ERD to Tables – N:M (Before)Student CourseN MEnrollsM:N - Add intersection table which includes primary keys from both tablesPerson # Course #Transforming ERD to Tables – N:M (After)Student CoursePerson # Course #Add intersection tableTransforming ERD to Tables – N:M (After)Student CourseN N1 1Person # Course #Flip relationship lines around so “many” side faces the intersection tableTransforming ERD to Tables – N:M (After)Student CourseN N1 1Person # Course #Person # Course #Add primary keys from original tables as foreign key fields in the intersection tableTransforming ERD to Tables – N:M (After)Student CourseRegistrationN N1 1Person # Course #Person # Course #Assign a name the intersection tableTransforming ERD to Tables – N:M (After)Student CourseRegistrationN N1 1Person # Course #Person # Course #GradeAdd other relevant fields to intersection table if necessaryTransforming ERD to Tables – N:M (After)Student CourseRegistrationN N1 1Person # Course #Person # Course #GradeWhat should be used as Primary Key in the intersection table? Composite Primary Key or AutonumberDetermining RelationshipsCustomer LoanTo determine the relationships between two entities, you have to ask the right questions.1) Can a single Customer have one or many Loans?2) Can a single Loan be related to one or many Customers?Determining RelationshipsCustomer Loan1 NThe answers to the two questions depend on the Bank’s business rules. For this example, we’ll say that a Customer can have many loans and a Loan can be related to only one Customer.Determining RelationshipsCustomer Loan1 N1) Can a single Loan have one or many Payments?2) Can a single Payment be applied to one or many Loans?PaymentsDetermining RelationshipsCustomer Loan1 NAgain, the answers depend on the Bank’s business rules. For this example, we’ll say that a Loan can have many Payments and a Payment can be applied to many