Transcript Lecture 5
IST 318 Database Administration Lecture 4 Data Modeling Topics Entities Relationship Attributes and keys Normalization Data Modeling Data modeling is trying to answer the WHAT questions What data items are needed? What are the relationships between these items? Data modeling is critical in database design Starting from collecting data requirements Providing a conceptual model of entities & relationships essential for communication and understanding A blueprint to build the physical database Benefits of a Sound Data Model Benefits Minimum data redundancy Maximum data integrity Better data sharing Increased consistency More timely access to data: performance Better data usability: overall Additional benefits New usages Data mining find out patterns hidden in normal data Three Rules to Follow Don’t think physical; think conceptual Focus on requirement from business perspective Avoid getting down to details such as storage issues and DBMS constraints Don’t think process; think structure The main point is to answer what need to be stored and what is the right way to represent the interrelationships Not when and how they are used Don’t think navigation; think relationship Focus on compiling the dictionary, don’t worry about in how many different ways it can be used Entities and Entity Occurrences Entity (also known as entity set) Similar to the concept of class Real-world or conceptual concepts that can be described via a number of attributes A template of entities of the same kind Examples: employee, department, location Entity occurrences (a simple entities) Similar to the concept of object Instances of certain types (or entity sets) Examples: Craig Mullin, Administrative, location #1 Attributes An attribute is a characteristic of an entity An attribute does one of the three things Identifies: uniquely identify a record Candidate key Becomes primary key when chosen Should be immutable Relates: link to an occurrence in another entity Foreign key Describes: depicts or express a characteristic of an entity occurrence The salary attribute of the employee entity Keys Candidate keys Sometimes it’s possible that more than sets of attributes can uniquely identify an occurrence of an entity Example: Both SSN and StudentId in the entity STUDENT (StudentId,LastName,FirstName,SSN, ...) Primary keys The one chosen from a set of candidate keys Foreign keys Identify a relationship between entity occurrences in two entities Relationships Defines how different entities relate to each other The keys define a relationship: PK-FK Naming Relationships Example: between COURSE and INSTRUCTOR COURSE is-taught-by INSTRUCTOR Entities are nouns/noun phrases Relationships are verbs/verb phrases Cardinality and Optionality Cardinality The number of occurrences that can exist between a pair of entities Each end of a relationship has a cardinality Also known as degree Typical cardinality terms: one, many Optionality Whether relationships are mandatory or optional Each end of a relationship has a optionality characteristic An Example In the employed-by relationship The STORE can have 0, 1, or many EMPLOYEEs If an EXPLOYEE exists, a relationship to a STORE is mandatory EMPLOYEE Employed by STORE Three Levels Conceptual data model Depict a high-level, business-oriented view of information Logical data model Offers a comprehensive formal structure that serves as a blueprint for business data Physical data model Transfers the logical data model into a physical implementation using a specific DBMS Normalization Normalization – first normal form First normal form eliminates repeating groups and nonatomic data from an entity Normalization – second normal form Second normal form ensures that all the attributes of each entity are dependent Normalization – third normal form Third normal form ensures that no relationships between attributes exist within an entity