180 likes | 540 Views
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.
E N D
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.
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
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.
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.
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.
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)
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.
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!)
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} ]
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.
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.
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.
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 ]
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]