data modeling 1
Post on 14-Jan-2016
4 Views
Preview:
DESCRIPTION
TRANSCRIPT
-
Introduction to Data ModelingCS 146
-
What is a Data Model?Definition: precise description of the data content in a system
Types of data models:Conceptual: describes WHAT the system containsLogical: describes HOW the system will be implemented, regardless of the DBMSPhysical: describes HOW the system will be implemented using a specific DBMS
-
Why do we need to create data models?
To aid in the development of a sound database design that does not allow anomalies or inconsistencies
Goal: to create database tables that do not contain duplicate data values that can become inconsistent
-
Types of Data ModelsEntity-Relationship (E-R) ModelsOnly addresses data and relationshipsClassic, simplestBest for deriving a sound table designMany extensions/variations existBasis for most other modeling approaches
UML (unified modeling language)Class modelsGoes beyond data, also models behaviors
-
Creating an Entity-Relationship Model
Identify entities
Identify entity attributes and primary keys
Specify relationships
-
Data EntitiesEntityA "thing" about which you want to store data in an applicationMultiple examples (instances) of the entity must existGoal:Store data about each entity in a separate tableDo not store duplicate data in multiple tables or recordsExamples: CUSTOMER, PRODUCT
-
Data Model Naming Conventions
Entity names are short, descriptive, compound word singular nounsUWEC_STUDENT, CANDY_PRODUCT, Entity names will ultimately correspond to table namesWhy singular?Makes more sense when you start talking about relationships
-
Data Entity InstancesEntity instanceA specific occurrence (data value) of an entityAn entity must have multiple entity instances or it is not really an entity!Examples: Davey Jones, Celestial Cashew Crunch
-
ER Model AttributesAttributeA characteristic (data field) of an entity that you want to store in the databaseExamples: CUST_ID, PROD_DESC
Attribute valueThe value of a particular attribute for a particular entity instanceExamples: 42, "Nuts Not Nachos"
-
Data Model Naming Conventions(continued)Attribute names are descriptive compound words that correspond to the entity nameAttribute names will ultimately correspond to field namesEvery attribute name within the database should be unique
-
ER Model NotationRepresent entities as rectanglesList attributes within the rectangle
EntityAttributesPrimary key
Table
-
Specific DBMS Naming ConventionsOracle, MySQL: words separated by underscoresEMPLOYEE, EMPLOYEE_NAME, candy_productWhy? Oracle forces all object names to upper-case letters, MySQL forces all names to lower-case on Windows
SQL Server: Use mixed caseEmployee, EmployeeNamePreserves mixed-case notation
To be safe, always separate words with underscores!
-
Attributes Selection IssuesPrimary keyAtomicCompositeMulti-valuedDerived
-
Primary Key Attributes
Attribute whose value is unique for every entity instanceEvery entity MUST have a PKDesignate by:Placing as first attribute in the entityUnderlineLabel using "PK"
Table
-
Selecting Primary KeysMust be values that are:Unique for every possible recordDo not changeBest practice: numeric with no blank spaces or formatting characters
Often you need to create a surrogate keyID value that serves only to identify the object in the databaseException: objects with "natural" primary keysSKUISBNVIN
-
Atomic and Composite AttributesAtomic attribute: represents a single data value15, Daniel", 12/25/2009
Composite attribute: can be decomposed into atomic attributes"James B. Brown""5580 Pinewood Road, Eau Claire, WI 54701"Should you ever allow a composite attribute in a database?
-
Composite AttributesDecompose into atomic components for:SortingSearchingFormatting
StudentStudent_IDStudent_NameStudent_AddressStudent_DOBStudent_ClassStudent_First_NameStudent_MIStudent_Last_NameStudent_Address_Line_1Student_Address_Line_2Student_CityStudent_StateStudent_CountryStudent_Postal_Code
-
Multi-Valued AttributesCan have multiple values for the same entityStudentStudent_ID (PK)Student_First_NameStudent_Last_NameStudent_AddressStudent_DOBStudent_ClassStudent_Phone1Student_Phone2EmployeeEmployee_ID (PK)Employee_First_NameEmployee_Last_NameEmployee_AddressEmployee_DOBEmployee_Dependent1Employee_Dependent2
-
Handling Multi-valued AttributesIf it has a definite maximum number, leave as a repeating attributeIf the upper limit is variable, make a new entity
StudentStudent_IDStudent_First_NameStudent_Last_NameStudent_AddressStudent_DOBStudent_ClassStudent_Phone1Student_Phone2EmployeeEmployee_IDEmployee_First_NameEmployee_Last_NameEmployee_AddressEmployee_DOBEmployee_Dependent1Employee_Dependent2DependentDependent_IDDependent_Namehas
-
Derived Attributes
Value that can be derived from other attributesStudent_Age = 22 (DOB = 11/20/1986, current date is 11/13/2009) Order_Total = $500 (Item 1 cost = $200, Item 2 cost = $300)
-
Handling Derived AttributesStore the underlying data values from which you can derive the attribute value Examples: DOB => AgeCurrentPrice and UnitsSold of an item (for a sales order)
unless the underlying values can change!PRODUCT_PRICE, COURSE_CREDITS
-
Creating an Entity-Relationship Model
Identify entities
Identify entity attributes and primary keys
Specify relationships
-
Data Model RelationshipsSpecify the number of instances of one entity that can be associated with instances of a related entityTypes:1:M1:1M:M
M denotes some value greater than 1 whose upper bound is undeterminedThis is called relationship cardinality
-
Example 1:M RelationshipStoreStore_IDStore_NameStore_AddressVideoVideo_IDVideo_TitleVideo_Format
Rents
Video_IDVideo_TitleVideo_Format1000The Princess BrideDVD1001SidewaysBluray1002Just VisitingDVD1003CrashBluray
Store_IDStore_NameStore_Address1Northside3233 Wisconsin St.2Southside4211 Golf Road
-
Example 1:1 RelationshipCustomerCustomer_IDCustomer_NameCustomer_Address
SpouseSpouse_IDSpouse_NameHas
Spouse_IDSpouse_Name52Ryan, Judy53Redmann, Rudy
Customer_IDCustomer_NameCustomer_Address1Ryan, Paul 5454 Hyde Court2Myers, Mary112 Birch Place
-
Example M:M RelationshipCustomerCustomer_IDCustomer_NameCustomer_Address
VideoVideo_IDVideo_TitleRents
-
Example ER Model
Table
-
Summary: The Data Modeling Process
Define entities
Define attributes
Define relationshipsIdentify relationship cardinality (1:1, 1:M, M:M)
top related