Database Concepts Review - Quezon City University (PDF)

WEEK 2 REVIEW OF DATABASE CONCEPTS IM101 - ADAVANCED DATABASE SYSTEMc WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS INFORMATION SYSTEM CONCEPTS A set of interrelated components that collect, manipulate, and disseminate data and information, and provide feedback to meet a...

WEEK 2 REVIEW OF DATABASE CONCEPTS IM101 - ADAVANCED DATABASE SYSTEMc WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS INFORMATION SYSTEM CONCEPTS A set of interrelated components that collect, manipulate, and disseminate data and information, and provide feedback to meet an objective Components of Information System 1. Data 2. Hardware 3. Software 4. Telecommunication 5. People 6. Procedures IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 3  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS FOUR STAGES OF PROCESSING Input: collect and introduce data to system Transaction: a business event, usually entered as input Data processing: perform calculations on input Output: what is produced by the information system Storage: vast amounts of data stored on optical discs IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 4 WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS DATA AND INFORMATION Data. Stored representations of meaningful objects and events. Structured: numbers, text, dates Unstructured: images, video, documents Example: Baker, Kenneth 324917628 Doyle, Joan E. 476193248 Finkle, Clive R. 548429344 IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 5 WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS DATA AND INFORMATION Information. Data processed to increase knowledge in the person using the data. Example: (a) Data in context (b) Summarized data IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 6  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS METADATA Metadata. Data that describes the properties and context of user data. Properties to describe Data names Length (or size) Allowable values Data context to describe Source of data Ownership Usage IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 7  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS METADATA NAME TYPE LENGTH MIN MAX DESCRIPTION SOURCE Couse Alphanumeric 30 Course ID and name Academic Unit Section Integer 1 1 9 Section number Registrar Semester Alphanumeric 10 Semester and year Registrar Name Alphanumeric 30 Student name Student IS Table 1.1 Example of Metadata IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 8  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS DATABASE SYSTEM ENVIRONMENT Database System. Refers to an organization of components that define and regulate the collection, storage, management and use of data within a database environment. Five major parts: hardware, software, people, procedures and data. IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 9  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS DATA MODELS A collection of concepts that can be used to describe the structure of a database that provides the necessary means to achieve this abstraction. IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 10  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS BASIC BUILDING BLOCKS OF DATA MODELS ENTITY Is a person, place, thing, or event about which data will be collected and stored. ATTRIBUTE is a characteristic of an entity. RELATIONSHIP describes an association among entities. IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 11  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS THREE TYPES OF RELATIONSHIP ONE-TO-MANY RELATIONSHIP one entity instance is associated with many instances of the related entity. MANY-TO-MANY entity is associated with many occurrences of a related entity and one occurrence of the related entity is associated with many occurrences of the first entity. ONE-TO-ONE one entity instance is associated with only one instance of the related entity. IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 12  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS CATEGORIES OF DATA MODEL 1. High-Level or Conceptual Data Model ▪ Provide concepts that are close to the way many users perceive data. 2. Representational or Logical Data Model ▪ Provide concepts that may be understood by end users but that, are not too far removed from the way data is organized within the computer. 3. Low-level or Physical Data Model ▪ Provide concepts that describe the details of how data is stored in the computer. IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 13  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS CATEGORIES OF DATA MODELCreating an Entity-Relationship Diagram (ERD) and associated Conceptual Design data dictionary to represent the reality and capture business data requirements. Transforming ERD to relational model: Tables, keys (constraints), etc. Logical Design Creating the database and other supporting structures based on a Physical Design specific DBMS. IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 14  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS BUSINESS RULE ✓Statements that define or constrain some aspect of the business ✓Properly written business rules are used to define entities, attributes, relationships and constraints. ✓A description of a policy, procedure, or principle within an organization. ✓Assert business structure ✓Control/influence business behavior ✓Expressed in terms familiar to end users ✓Automated through DBMS software IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 15  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS IMPORTANCE OF BUSINESS RULE It helps to standardize the company’s view of data. It can be a communication tool between users and designers. It allows the designer to understand the nature, role, and scope of the data. It allows the designer to understand business processes. It allows the designer to develop appropriate relationship participation rules and constraints and to create an accurate data model. IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 16  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS GOOD BUSINESS RULE IS: ✔Declarative–what, not how ✔Precise–clear, agreed-upon meaning ✔Atomic–one statement ✔Consistent–internally and externally ✔Expressible–structured, natural language ✔Distinct–non-redundant ✔Business-oriented–understood by business people IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 17  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS ENTITY RELATIONSHIP DIAGRAM A data modeling technique that creates a graphical representation of the entities, and the relationships between entities, within an information system. IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 18  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS ENTITY RELATIONSHIP DIAGRAM Three basic elements in ER models: Entity - are the "things" about which we seek information. - is a person, object, place or event for which data is collected. Attributes - are the data we collect about the entities. Relationship - provide the structure needed to draw information from multiple entities or the link between entities. IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 19  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS ENTITY RELATIONSHIP DIAGRAM ENTITY May be an object with a physical existence (for example, a particular person, car, house, or employee) or it may be an object with a conceptual existence. IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 20  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS ENTITY RELATIONSHIP DIAGRAM ATTRIBUT ES Particular properties that describe it. IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 21  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS ENTITY RELATIONSHIP DIAGRAM Types of Attributes: Key Attribute - is the unique, distinguishing characteristic of the entity. KEY ATTRIBUTE Composite Attribute - can be divided into smaller subparts, which represent more basic attributes with independent meanings. COMPOS ITE ATTRIBUTE COMPOS ITE IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 22  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS ENTITY RELATIONSHIP DIAGRAM Types of Attributes: Required Attributes - is an attribute that must have a value; in other words, it cannot be left empty. Optional Attribute - is an attribute that does not require a value; therefore, it can be left empty. Single-Valued Attributes - is an attribute that can have only a single value. IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 23  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS ENTITY RELATIONSHIP DIAGRAM Types of Attributes: Multi-valued Attribute - can have more than one value. MULTI- VALUED Derived Attribute - is an attribute whose value is calculated (derived) from other attributes. The derived attribute need not be physically stored within the database; instead, it can be derived by using an algorithm. DERIVED IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 24  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS ENTITY RELATIONSHIP DIAGRAM RELATIONS HIP Provide the structure needed to draw information from multiple entities or the link between entities. IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 25  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS ENTITY RELATIONSHIP DIAGRAM DEGREE OF RELATIONSHIP ▪ is the number of entity types that participate in it. 1. Unary Relationship : One entity related to another of the same entity type. PERSON MARRIE D IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 26  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS ENTITY RELATIONSHIP DIAGRAM DEGREE OF RELATIONSHIP 2. Binary Relationship : Entities of two different types related to each other. EMPLOYE HAS PROJECT E IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 27  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS ENTITY RELATIONSHIP DIAGRAM DEGREE OF RELATIONSHIP 3. Ternary Relationship : Entities of three different types related to each other. PART SUPPLI WAREHOUS VENDOR ES E IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 28  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS ENTITY RELATIONSHIP DIAGRAM DEGREE OF RELATIONSHIP 4. N’ary Relationship : Entities of three or more different types related to each other. PHYSICIA N SUPPLI PATIENT ES NURSE SUPPLIES IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 29  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS ENTITY RELATIONSHIP DIAGRAM CARDINALITY OF RELATIONSHIP ▪Describes how many entity instance can be in the relationship. 1. One-to-one : Each entity in the relationship will have exactly one related entity. 2. 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. 3. Many-to-many : Entities on both sides of the relationship can have many related entities on the other side IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 30  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS ENTITY RELATIONSHIP DIAGRAM CARDINALITY OF RELATIONSHIP ▪A patient history is recorded for one and only one patient. ▪A patient must have recorded at least one history, and can have many. RECORD PATIENT ED HISTORY IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 31  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS ENTITY RELATIONSHIP DIAGRAM CARDINALITY CONSTRAINTS ▪The number of instances of one entity that can or must be associated with each instance of another entity. ▪Minimum Cardinality If zero, then optional If one or more, then mandatory IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 32  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS ENTITY RELATIONSHIP DIAGRAM CARDINALITY CONSTRAINTS ▪A patient history is recorded for one and only one patient. ▪A patient must have recorded at least one history, and can have many. RECORD PATIENT ED HISTORY IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 33  WEEK 2 – REVIEW OF DATABASE SYSTEM CONCEPTS References: Elmasri, Ramez & Navathe, Shamkant B.(2016). Fundamentals of Database Systems. (7th Edition), Coronel, Carlos & Morris, Steven. (2017). Database System Design: Design, Implementation and Management. (12th Edition). IM101 – ADVANCED DATABASE IM101 – ADVANCED DATABASE 34 35