Designing Database Applications Walid G. Aref
Roadmap for Designing Database Applications 1. Analyzing real-world applications • Real-world application side: Domain experts • To tell their story – What they need • DBMS side: System analyst • Map the domain expert needs to a model that the database developers can use to Design the database application • Use Modeling Language • UML ( The Unified Modeling Language ): A general-purpose, developmental, modeling language that is intended to provide a standard way to visualize the design of a system • ER Model (The Entity-Relationship Model): • High-level data model • Define the data elements and relationships for a specified system • Provide a conceptual design for the database • Very simple and easy to design view of data. • Output from this process: High-level Design Document • Includes ER diagrams • Mockup screen designs (to reflect update screens, query screens, reports screens) • Workflows of processes
Roadmap for Designing Database Applications 2. Transferring the design document to the relational model • Input: Design Document (ER Diagrams) • Output: Relational schemas • Tools: DB Design tools (or manually) 3. Eliminating Redundancy • Input: Relational schemas • Output: Optimized relational schemas • Mathematical foundation: Normalization Theory • Tools: DB Design tools (or manually) 4. Developing the database application software • Input: Optimized relational schemas, screen designs, process workflows • Output: Database Application
Database Design Progress: Step 1 – Entity Relationship Modeling • Analyze the Real-world Scenario • Use the Entity-Relationship Model (ER Model) in this Step • Output is Conceptual Design Document • Identify the Entities and the Relationships in the Real-world Scenario • Identify the Attributes of both the Entities and the Relationships • Reflects the information we want to store in the database about these entities and relationships • Identify the integrity constraints that need to hold for this real-world application • Produce a pictorial database "schema” ( ER diagram )
Building Blocks of the ER Model iName iRank iid • Entities (E) and Relationships (R) • The ER Diagram represents the design Instructor iSalary • Entity: • Real-world object that is of interest in the scope of this real-world scenario. • Example entities: Instructor, Student, Course, Classroom, Textbook, Restaurant • Each entity is described using a collection of attributes • Attribute: • Describes an entity • Example attributes for Entity Instructor: iid, iName, iRank, iSalary, iOfficeNumber, iPhoneNumber, iAddress • Each attribute has a type (domain) • Entity Set: • A class or group of entities that share the same attributes, e.g., the set of instructors • Each entity set has one or more attributes that serve as the key • ER Diagram: • Entity Set shows as a Rectangle • Attribute shows as an Oval • Key of the Entity set is underlined, e.g., iid in the figure
Our Example Relational Database Schema • Students(sid: string, name: string, login: string, age: integer, gpa: real) • Courses(cid: string, cname: string, credits: integer) • Enrolled(sid: string, cid: string, grade: string) • Instructor(iid: string, iname: string, irank: string, isalary: real) • Teaches(iid: string, cid: string, year: integer, semester: string) 6
En Entity y Sets s for Our Example Relational Database Schema • Students(sid: string, name: string, login: string, age: integer, gpa: real) • Courses(cid: string, cname: string, credits: integer) • Enrolled(sid: string, cid: string, grade: string) • Instructor(iid: string, iname: string, irank: string, isalary: real) • Teaches(iid: string, cid: string, year: integer, semester: string) 7
Relationships R1 E1 E2 Binary Relationship • A Relationship associates two or more entities together • A relationship can have attributes • A Relationship Set : A set of similar relationships among the same group of entities with the same attributes • Binary and n-ary relationship sets Employee • An entity set can participate in multiple relationship sets Managed by Manager of • An entity set can participate in the same relationship set with different “ roles ” • ER Diagram: Relationship show as a diamond Manage
Relationship Sets in Our Example Relational Re Database Schema • Students(sid: string, name: string, login: string, age: integer, gpa: real) • Courses(cid: string, cname: string, credits: integer) • Enrolled(sid: string, cid: string, grade: string) • Instructor(iid: string, iname: string, irank: string, isalary: real) • Teaches(iid: string, cid: string, year: integer, semester: string) cname iName cid iid iRank Courses Instructor credits iSalary Teaches year semester 9
Examples of Ternary Relationships • This student is taking this course with this instructor in this semester Student Courses Takes Instructor year semester
Examples of Ternary Relationships • This student is taking this course with this instructor in this semester 1 st Entity 2 nd Entity 3 rd Entity Attribute • Can semester be an entity as well? Student Courses Takes Instructor year semester
Can we Model a Ternary Relationship with Multiple Binary Relationships? Student • Will need to add a new entity • Each course offering will represent a triplet R1 Student year Course Courses Takes Instructor Offering semester year semester R2 R3 • Degree of a relationship = # of entities Instructor Courses
Cardinality Constraints in Relationship Sets • 1-1, 1-m, m-1, m-m relationships • An element may not participate in the relationship Entity • For example, • => Partial participation 1-to-1 1-to Many Many-to-1 Many-to-Many Relationship Relationship Relationship Relationship
Cardinality Constraints in Relationship Sets • Total Participation: • Every entity in the entity set participates in the relationship • Example: Every course should have an instructor • Thus, every course participates in the Teaches relationship set cname iName cid iid iRank Courses Instructor credits iSalary Teaches year semester
Cardinality Constraints in Relationship Sets • Total Participation: • Every entity in the entity set participates in the relationship • Example: Every course should have an instructor • Thus, every course participates in the Teaches relationship set • ER Diagram: • Use bold line to indicate total participation cname iName cid iid iRank Courses Instructor credits iSalary Teaches year semester
Cardinality Constraints in Relationship Sets • One-to-one relationship: • Instructor can teach at most one course • ER Diagram: • Use arrow at Courses side cname iName cid iid iRank Courses Instructor credits iSalary Teaches year semester
Cardinality Constraints in Relationship Sets • One-to-one relationship: • Instructor can teach at most one course • A course can be taught by at most one instructor • ER Diagram: • Use arrow at Courses side • Use arrow at Instructor’s side cname iName cid iid iRank Courses Instructor credits iSalary Teaches year semester
Cardinality Constraints in Relationship Sets • One-to-Many Relationships • A course can be taught by at most one instructor • An instructor can teach more than one course • ER Diagram: • Use arrow at Instructor’s side (One side) • Use straight line at Courses side (Many side) cname iName cid iid iRank Courses Instructor credits iSalary Teaches year semester
Cardinality Constraints in Relationship Sets • Many-to-One Relationship • A course can be taught by more than one instructor • An instructor can teach at most one course • ER Diagram: • Use arrow at Courses side (One side) • Use straight line at Instructor’s side (Many side) cname iName cid iid iRank Courses Instructor credits iSalary Teaches year semester
Cardinality Constraints in Relationship Sets • Many-to-Many Relationships • An instructor can teach more than one course • A course can be taught by more than one instructor • ER Diagram: • Use straight line at Courses side (Many side) • Use straight line at Instructor’s side (Many side) cname iName cid iid iRank Instructor Courses credits iSalary Teaches year semester
Example of Combining Cardinality Constraints in Relationship Sets • One-to-Many Relationship + Total Participation • Every course must be taught by exactly one instructor • An instructor can teach more than one course • ER Diagram: • Use arrow at Instructor’s side (One side) • Use bold straight line at Courses side (Many side) cname iName cid iid iRank Courses Instructor credits iSalary Teaches year semester
More Complex Participation Constraints • A line in one side of a relationship may be annotated by a minimum and maximum cardinalities. • Appears in the form: LowerLimit-UpperLimit • LowerLimit • A LowerLimit of 1 means total participation. • A LowerLimit of 0 means partial participation. • A LowerLimit of m means at least m participations in the relationship • UpperLimit • An UpperLimit of 1 means ”at most one relationship”. • An UpperLimit of * means Many (no limit). • An UpperLimit of n means at most n participations in the relationship
Recommend
More recommend
