IS112 Data Management Summary Notes PDF

Preview

Summary

Download IS112 Data Management Summary Notes PDF

Description

IS112 Data Management DM CHAPTER 1: THE DATABASE ENVIRONMENT .................................................................................. 2 DATA .................................................................................................................................................. 2 INFORMATION ................................................................................................................................... 3 METADATA......................................................................................................................................... 4 THE DATABASE APPROACH ................................................................................................................ 4 DM CHAPTER 2: MODELING DATA IN ORGANISATION .......................................................................... 5 BUSINESS RULE .................................................................................................................................. 5 DATABASE ANALYSIS.......................................................................................................................... 6 THE E-R MODEL.................................................................................................................................. 6 ENTITY ............................................................................................................................................ 7 ATTRIBUTES ................................................................................................................................... 8 IDENTIFIERS.................................................................................................................................... 9 RELATIONSHIP.................................................................................................................................. 11 RELATIONSHIP WITH ATTRIBUTES ............................................................................................... 11 DEGREE OF RELATIONSHIP........................................................................................................... 13 CARDINALITY .................................................................................................................................... 13 CARDINALITY OF RELATIONSHIP .................................................................................................. 13 RELATIONSHIPS AND CARDINALITY ............................................................................................. 14 CARDINALITY CONSTRAINTS ........................................................................................................ 15 READING CARDINALITY ................................................................................................................ 16 STRONG VS WEAK ENTITY, AND IDENTIFYING RELATIONSHIPS ....................................................... 17 WEAK ENTITY ............................................................................................................................... 18 ASSOCIATIVE ENTITIES ................................................................................................................. 18 DM CHAPTER 3: THE ENHANCED E-R MODEL ...................................................................................... 20 SUPERTYPES AND SUBTYPES............................................................................................................ 20 RELATIONSHIPS AND SUBTYPES................................................................................................... 21 GENERALISATION AND SPECIALISATION .......................................................................................... 22 GENERALISATION ......................................................................................................................... 22 SPECIALISATION ........................................................................................................................... 22 CONSTRAINTS IN SUPERTYPE........................................................................................................... 24 DISJOINTNESS CONSTRAINT......................................................................................................... 24 SUBTYPE DISCRIMINATORS.......................................................................................................... 25 DM CHAPTER 4: LOGICAL DATABASE DESIGN ..................................................................................... 26 RELATION ......................................................................................................................................... 26 1|Page

CORRESPONDENCE WITH ER MODEL .............................................................................................. 27 PRIMARY KEYS ................................................................................................................................. 27 INDEXES ........................................................................................................................................... 28 RELATIONAL SCHEMA ...................................................................................................................... 29 FOREIGN KEY and INTEGRITY CONSTRAINTS ................................................................................... 30 INTEGRITY CONSTRAINTS............................................................................................................. 31 ENTITY INTEGRITY ........................................................................................................................ 31 DOMAIN CONSTRAINTS ............................................................................................................... 31 MAPPING EER DIAGRAMS INTO RELATIONS (SUPER IMPORTANT FOR FINALS).............................. 32 1. MAPPING REGULAR ENTITIES TO RELATIONS .......................................................................... 32 2. MAPPING WEAK ENTITIES ........................................................................................................ 33 3. MAPPING BINARY RELATIONSHIPS .......................................................................................... 34 4. MAPPING ASSOCIATIVE ENTITIES ............................................................................................ 36 5. MAPPING UNARY RELATIONSHIPS ........................................................................................... 37 6. MAPPING TERNARY RELATIONSHIPS ....................................................................................... 38 7. MAPPING SUPERTYPE/SUBTYPE RELATIONSHIPS .................................................................... 39

DM CHAPTER 1: THE DATABASE ENVIRONMENT DATA Defn: DATA are stored representations of meaningful objects that are relevant to the organisation and events. •

•

Structured Data o Have formats o Examples: Numbers, texts, dates Unstructured Data o No formats o Examples: Images, videos, document o Email addresses is another example. Although email address has a fixed format, the text part of an email is unstructured. Thus, email addresses are unstructured data.

2|Page

Figure 1: Raw data

Figure 2: Data

Figure 1 has a list of facts that satisfy the definition of data, but they are useless in their present form because there is no information about what it is. On the other hand, figure 2 is known as a type of data about the class roster.

INFORMATION Defn: INFORMATION are data processed to increase knowledge in the person using the data. Information are the end product after the data collected has been processed. Graphical displays turn data into useful information that managers can use for decision making and interpretation.

3|Page

Figure 3: Summarised Data/Information

METADATA Defn: METADATA are data that describes the properties and context of user data. Metadata are the descriptions of the properties or characteristics of the data, including data types, field sizes, allowable values and documentation

Figure 4: Example of metadata for class roster

THE DATABASE APPROACH Defn: DATABASE are data that describes the properties and context of user data. Database are the central repository of shared data. Data is managed by a controlling agent and stored in a standardized, convenient form. These require the use of a database management system (DBMS). The minimum requirements of a DBMS are that it should be efficient (fast services), reliable (24/7, 365 days), secure etc.

4|Page

Defn: DATABASE MANAGEMENT SYSTEM (DBMS) is a data storage and retrieval system which permits data to be stored non-redundantly while making it appear to the user as if the data is wellintegrated. In other words, DBMS is a software system that create, maintain and provide controlled access to databases. We need database approaches to manage data through integration and data sharing, minimize data redundancy and program-data independence. Costs of the database approach: • • • • •

New, specialized personnel Installation and management cost and complexity Conversion costs Need for explicit backup and recovery Organisational conflict

DM CHAPTER 2: MODELING DATA IN ORGANISATION BUSINESS RULE Defn: BUSINESS RULE is a statement that defines or constrains some aspect of the business. Business rules are defined to assert business structure or to control or influence the behavior of the business. It should be expressed in terms that are familiar to the end users. Criteria of a good business rule: • • •

• • • •

Consistency – cannot have contradictory statemen internally and externally Expressible – no legal languages that non-law people cannot understand. Use structure and natural language Precise – Must be clear o Bad Example: A student may register for a course if he or she can – when can/cannot? o Good Example: A student may register for a course only if he or she has successfully completed the prerequisites for that course Distinct – Not redundant Business-oriented – Easily understood by business people Declarative – what, not how Atomic – one statement

5|Page

DATABASE ANALYSIS Database analysis is used to identify and identify those rules that govern date. It represents those rules so that they can be unambiguously understood by information systems developers and users. We care about business rules that affect data processing and storage: • • •

A student may register for a section of a course only if he or she has successfully completed the prerequisites for that course (good) One section has at most 50 students (good) Students are allowed to wear casual dresses (bad – not required during the designing of the database as you are only storing student’s data and not how they dress)

THE E-R MODEL Defn: ENTITY-RELATIONSHIP MODEL (E-R model) is a logical representation of the data for an organisation or for a business area. Defn: ENTITY-RELATIONSHIP DIAGRAM (E-R diagram) is a graphical representation of an entityrelationship model (end product). Note: In this course, drawing an ER model/ER diagram is the same. Basic ER notations consists of entity symbols, attribute symbols and relationship symbols as shown in the figure below.

Figure 5: ER Notations

6|Page

ENTITY Defn: ENTITY is a person, place, object, event or concept in the user environment whereby the organisation wishes to maintain data. Entity should have at least 2 attributes and an identifier (except subtype). Examples of entity: • • •

Person: employee, student, patients Place: store, warehouses, states Object: machine, building, automobile

*Hint: Most nouns are entity Defn: ENTITY SYMBOL are drawn as a rectangular box with the entity written inside the box.

(Entity Symbol)

Defn: ENTITY TYPE is a collection of entities that share common properties or characteristics. Defn: ENTITY INSTANCE is a single occurrence of an entity type. What should an entity be? •

•

Entity should be: o An object that will have many instances in the database o An object that will be composed of multiple attributes o An object that we are trying to model Entity should NOT be: o A user of the database system o An output of the database system (eg. a report)

Entity Type

Two Instances

Figure 6: Entity Type with 2 instances

7|Page

ATTRIBUTES Defn: ATTRIBUTE is the property or characteristic of an entity type. Attributes names should be inside the entity rectangle Classification of attributes: 1. Simple vs Composite Attribute A composite attribute is an attribute that has been broken into different components. Use parentheses (“””) to store composite attributes. The different values inside a composite attribute is known as component attributes. For component attributes, use ().

Figure 7: A compo

Composite Attribute

Component Attribute

Employee_Address Name

Age Street_Address

City

State

Postal_Code

Simple Attribute Figure 8: Example of Composite attribute in excel form:

2. Single-Valued vs Multi-Valued Attribute For multi-valued attributes, use {}. If you know the employee have 2 skills each, you can create 2 simple attributes – skill 1, skill 2. However, you don’t know how many skills the employee actually has, thus you use multivalued attributes.

8|Page

Figure 9:Multivalued and Derived Attributes

Employee_Name Employee_ID

Payroll_ Address

Date_ Employed

Skill

Years_ employed

First

Last

Street

Zip

001

Jacky

TAN

Hill Street 100

164607

May 01, 1999

C/C++/Java

18

002

Susan

LIM

Stamford Road 80

178902

Oct 14, 2000

Java/.Net

17

Figure 10: Multivalued and Derived attributes in excel form

3. Stored vs Derived Attribute As seen in figure 9, for derived attribute, we use []. You must know how these are derived. Unless it is mention in the question, you don’t have to store derived attributes. For instance, in figure 10, the years employed is derived from the date of employment.

4. Identifier Attribute To differentiate an identifier attribute, you underline it. There will be no repeat of this identifier throughout all the instances available. Every entity SHOULD have an identifier. For example, one potential identifier of an entity, student, should be the student ID. An entity may have multiple candidate identifier, but will only need 1 identifier. You should choose the most simpler identifier.

IDENTIFIERS Defn: Identifier is an attribute or combination of attributes that uniquely identifies individual instances of an entity type. Candidate identifier is an attribute than could be an identifier. As mentioned above, an entity may have multiple candidate identifiers, but will only need one identifier. • •

Characteristics of identifiers: Will not change in value Will not be null: every instance must have a value for the identifier 9|Page

•

Use simple identifiers instead of long, composite identifiers

Simple vs Composite Identifier Attributes For a composite identifier, there will be 2 underlined attributes. However, it will still be considered as 1 identifier – composite identifier. Both design of composite identifier attribute shown in figure 11 are okay. Only use composite identifier if you cannot find a simple attribute as an identifier.

Figure 11: Composite Identifier

10 | P a g e

RELATIONSHIP Defn: RELATIONSHIP TYPE captures the meaningful association between (or among) entity types. The relationship type is modeled as lines between entity types whereas relationship instance is between specific entity instances. Relationships can have attributes and these relationships describe features related to the association between the entities in the relationship. Two entities can have more than one type of relationship between them (multiple relationships)

Figure 12: Relationship Type

Figure 13: Relationship Instances

RELATIONSHIP WITH ATTRIBUTES When the attributes depend on all the entities that participate in the relationship, the attributes belong to the relationship.

11 | P a g e

Suppose you want to find out which employee took what course and also know the grades and completed date of that particular course. Draw the ER diagram. Wrong examples (figure 14 and 15):

EMPLOYEE COURSE

Employee_ID Emplolyee_Name Birth_Date Complete_Date Grade

Completes

Course_ID Course_Title {Topic}

Figure 14: Wrong Example 1

This example is wrong because when you store it this way, each employee will only have one complete date and one grade regardless of the amount of course she take which is wrong.

COURSE EMPLOYEE Employee_ID Emplolyee_Name Birth_Date

Completes

Course_ID Course_Title {Topic} Complete_Date Grade

Figure 15: Wrong Example 2

This example is also wrong because it shows that all the employees that completed that particular course will share the same grade and completed date.

Completed _Date Grade EMPLOYEE Employee_ID Emplolyee_Name Birth_Date

COURSE

Completes

Course_ID Course_Title {Topic}

Figure 16: Correct Example

Each employee can complete different course at different complete date with a different grade. In addition, each course can also be taken by different employees at different completion date and grades. We need relationship with attributes that allows you to derived attributes from the two entities. It is shown by the dotted line connected to the relationship lines.

12 | P a g e

DEGREE OF RELATIONSHIP The degree of a relationship states the number of entities participating in the relationship: • • • •

Unary relationship (1 entity involve) Binary relationship (2 entities involve) Ternary relationship (3 entities involve) More than 3 àpossible but a rare case

Figure 17: Degree of relationship

CARDINALITY CARDINALITY OF RELATIONSHIP Defn: CARDINALITY OF RELATIONSHIP refers to the number of instances of one entity that can or must be associated with each instance of another entity. There are 3 types of cardinality of relationship: •

One – to – One: each entity in the relationship will have exactly one related entity

Male

Is Married To

Female

Peter

Mary

Mike

Susan

Jacky

Emily

Tommy

Jennifer

Figure 18: One - to - One relationship

•

One – to – Many: An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity.

13 | P a g e

Manager

Manages

Employee Mary

Peter

Emily Susan Jacky

Jennifer

Figure 19: One - to - Many relationship

•

Many – to – Many: Entities on both sides of the relationship can have many related entities on the other side.

Course

Take

Student

IS101

Mary

IS200

Susan

IS201

Emily

IS202

Jennifer

Figure 20: Many - to - Many relationship

RELATIONSHIPS AND CARDINALITY