1 / 16

Logical Relational Database Design

Logical Relational Database Design . Logical Relational Design. Purpose of logical data design is to represent application data in the form of related 2-dimensional relations. These will be used to create the tables in a relational database for the application.

arlen
Download Presentation

Logical Relational Database Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Logical Relational Database Design

  2. Logical Relational Design • Purpose of logical data design is to represent application data in the form of related 2-dimensional relations. These will be used to create the tables in a relational database for the application. • Details are obtained by analyzing samples of a wide range of user views from the application.

  3. User view • A user view is a view of the data presented by the application to the user • User views may include: input screen, output screen, input form, summary report, detailed report (most important type of user view is one that is used to input data) • User view may be either an actual sample or a prototype • User view should contain sample data

  4. User View Analysis Steps taken to represent a user view as a collection of normalized relations: • Identify and list all attributes for the user view as a relation. • Choose the most likely primary key for the relation. • Determine whether an attribute is single-valued or repeats either individually or as part of a set of related attributes. • Normalize each relation.

  5. Normalization • Technique used to analyze data is called normalization. • Applying the technique of normalization to as user view allows data presented in a complex form to be expressed in the form of 2 dimensional relations. • These relations will become tables in the relational database for the application.

  6. Normalization (ctd) • The purpose of normalization is to have all data in a relation functionally dependent on the primary key of the relation (i.e. The value for each attribute can be determined by the whole key and nothing other than the key). • Normalization requires that a primary key be identified for each relation.

  7. Primary Key • Identify potential primary keys : candidate keys (eg SIN, Student Number) • Choose as primary key the attribute(s) that best uniquely identify the information presented by the user view • A primary key may be an actual attribute (eg School Name) or more commonly may be an assigned value (eg Student Number, SIN)

  8. Dependencies • Depending on the primary key chosen for a relation the following dependencies may exist in a relation: multi-valued, partial, transitive and functional. • A normalized relation can only contain functional dependencies. • Normalization resolves multi-valued, partial and transitive dependencies.

  9. Sample User View: Class List

  10. Sample User View: Class List • To reduce the complexity of this example I am assuming that only one instructor is assigned to teach a section of a subject (although this is NOT true for summer semesters at our school!)

  11. Un-normalized Class List Relation • Relational Notation: • [ ] : contains list of attributes for relation • A, B : attribute or attributes that are the Primary Key • { } : attribute or group of attributes that have more than one value for a single value of the primary key • CLASSLIST [ Subject Code, Section Code, Instructor No, Instructor Name, Subject Name, {Student Number, Student Name} ]

  12. Multi-valued Dependency • A multi-valued dependency (also known as a repeating group) is when a single value of the primary key determines 1 or more than one value of a non-key attribute; A ->> B • For example Subject Code and Section Code as the primary key of the Class List relation determine more than one value of Student Number.

  13. Partial Dependency • A partial dependency is when only a part of a composite primary key determines the value of a non-key attribute; • A, B -> C but actually B -> C • For example Subject Code and Section Code together are the primary key for a section of a subject but Subject Code alone determines the value of Subject Name.

  14. Transitive Dependency • A transitive (or indirect) dependency is when the primary key determines the value of a non-key attribute that then determines the value of another non-key attribute; if A -> B, C but we can determine that B -> C then we can say A -> B and B -> C • For example Subject Code and Section Code together are the primary key for a section of a subject and determine the value of Instructor No which then determines the name of the assigned instructor.

  15. Normalization • To normalize a relation we must identify and resolve all dependencies other than full functional dependencies by creating new relations. • If in the unnormalized relation: [A, B, {C, D,} E, F, G ] if we know that F -> G (transitive) and that B -> E (partial) and that A,B ->> C, D then we will have as resulting normalized relations: [A, B, C, D ] ; [B, E] ; [A, B, F ] ; [F, G ]

  16. Actual 3NF Relations for Class List • [Subject Code, Subject Name ] • [Instructor No, Instructor Name ] • [Student Number, Student Name ] • [Subject Code, Section Code, Instructor No] • [Subject Code, Section Code, Student Number]

More Related