Information Management CC 106 2024-2025 1st Semester PDF

INFORMATION MANAGEMENT | CC 106 | 2024 – 2025 | 1st Semester TYPES OF INFORMATION UNIT 1 DATABASE: BASIC CONCEPTS & DATA IN CONTEXT APPLICATIONS...

INFORMATION MANAGEMENT | CC 106 | 2024 – 2025 | 1st Semester TYPES OF INFORMATION UNIT 1 DATABASE: BASIC CONCEPTS & DATA IN CONTEXT APPLICATIONS - Requires you to read it (ex. Texts) DATA SUMMARIZED DATA - Data (plural for datum) is referred to as any - In a form of graphs (ex. Pie chart) raw facts or concepts. These are usually - Graphical displays useless in their natural form because the meaning that they imply is vague. DBMS (DATABASE MANAGEMENT SYSTEM) - Whatever comes in the database. - A software package that allows users to design, create, access, maintain, INFORMATION manipulate, and retrieve data and - Once data that have been processed so as information. the meaning would be consistent and be - CRUD (create, read, update, delete/archive) vividly understood by those who use them, they are turned into information. HIERARCHY OF DATA - It can be done by organizing them, presenting them in a given context, CHARACTER summarizing them, or presenting them for - A character, equivalent to a byte, can be a human interpretation in the form of a graph. letter, a number, special symbol, punctuation mark, or space. TYPES OF DATABASES FIELD STRUCTURED - A file is usually a combination of one or - Contains numbers and values more related characters identified by a field - Can be put to tables neatly (ex. Numbers, name. short texts, dates, etc.) RECORD UNSTRUCTURED - A record is equivalent to one row of a - Consists of sensory, text files, audios and file/table or only a related field group. videos - More commonly known as qualitative data DATA FILE - Cannot be processed using conventional data tools - A data file is a collection of interrelated records. In layman’s term, it merely refers to VARIABLES a table of values. - Varies in size DATABASE METADATA - A database is a collection of interrelated data files or tables. - Characteristics of data - Describes the data / what is the data FILE SYSTEM AND DATABASE SYSTEM - Provides info about one or more aspects of data FILE MANAGEMENT SYSTEM - Used to summarized basic info about data - Computerized and dependent in a program - Redundant 1  INFORMATION MANAGEMENT | CC 106 | 2024 – 2025 | 1st Semester - Limited data sharing FRONT END / UI > DBMS > BACK END / DB DATABASE SYSTEM > PRESENCE OF DBMS > RANGE OF DB APP RELATIONAL PERSONAL DB - Can recover data even when the program - One user at a time. crash - Single user - Program data independence - Possible sharing across the company MULTITIER CLIENT / SERVER DB - Consistent and preserve data integrity - Multi user DATABASE APPROACH 1. Develop a list of high-level entities CLIENT TIER a. ENTITY - an object or concept - Work group important to business 2. Establish the business rules APP / WEB TIER a. BUSINESS RULES - the - You can access as long as you have access associations (relationships) to internet 3. Develop an enterprise data model a. ENTERPRISE DATA MODEL - a ENTERPRISE TIER graphical model that shows the high- - Must have wired / wireless connection level entities for the organization and the associations among those ENTERPRISE DB entities i. COMPONENTS: - Entire organization is the scope 1. Attribute 2. Relationship CENTRALIZED DB 3. entity - Client / server approach - Server only CHARACTERISTICS FO DB APPROACH 1. DBMS is required – sharing and integration OPERATIONAL DB is allowed - Wording 2. SELF DESCRIBING – has own metadata - Support the day-to-day operations of the 3. PROGRAM INDEPENDENCE organization 4. SUPPORT MULTIPLE DATA VIEWS – - Support the primary database operations. provide lots of access. 5. MULTIUSER SYSTEM DATAWARE HOUSE DATABASE APPLICATION AND DATABASE - Usually holds big data and store info from TYPES various data sources. DATABASE APPLICATION ETL (EXTRACT > TRANSFORM > LOAD) - software programs developed to collect, - Extract, transform in an acceptable format, manage, and provide information efficiently and load to the targer database. to end-users. These programs allow THE DATABASE ENVIRONMENT AND USERS searching, sorting, manipulating, and sharing of information. HARDWARE - Allows users to manipulate data in a DBMS. 2  INFORMATION MANAGEMENT | CC 106 | 2024 – 2025 | 1st Semester - Refers to all tangible or physical devices o Continuation of planning phase used to manage the database. - Design o Local DB design, physical DB design SOFTWARE and definition - Intangible elements that help control and - Implementation manage the database and its contents o Data implementation - Maintenance DB SYSTEM ENVIRONMENT o Data maintenance - Hardware THREE LEVEL ARCHITECTURE AND DATA - Software INDEPENDENCE o Dbms o Os EXTERNAL LEVEL o App programs - Also known as the user view or external - People schema o Sys admin - Presents data in a format that is o Db admin understandable and usable for end-users, o Db designer often represented through screens, reports, o Sys analysts and app dev. or business transactions o End users (data entry operator) - Procedures CONCEPTUAL LEVEL - Data - Acts as an intermediary between the - DB Users external and internal levels o DB and Data Admin - Describes what data is stored in the o DB Designer database and the relationships among o Tool Dev. those data entities o Software Engr. o End Users INTERNAL LEVEL ▪ Casual – managers / supervisors - Also referred to as the physical schema, is ▪ Naïve or parametric – the lowest layer of database architecture beginner (ex. Cashier) - Specifies how data is stored on hardware, ▪ Sophisticated – knows including file structures and access methods complexity of the system LEVEL DESCRIP. FOCUS ▪ Stand-alone External User-specific User views of data interaction DB DEVELOPMENT PROCESS with relevant SYSTEM DEVELOPMENT LIFE CYCLE data - A traditional and time-tested way of creating Conceptual Overall Data effective information system structure of entities, the DB relationship, PLANNING > ANALYSIS > DESIGN > and IMPLEMENTATION > MAINTENANCE constraints Internal Physical Data - Planning storage storage o Enterprise modeling, conceptual details methods data modeling and - Analysis perfomance 3  INFORMATION MANAGEMENT | CC 106 | 2024 – 2025 | 1st Semester DATA INDEPENDENCE a. structured, natural language 6. Distinct PHYSICAL DATA INDEPENDENCE a. non-redundant - Refers to the ability to change the internal 7. Business-oriented schema (or physical schema) of a database a. understood by business people without affecting the conceptual schema or E-R MODEL CONSTRUCTS external views. - Modification without affecting how the users ENTITIES interact with the data - Entity instance CONCEPTUAL DATA INDEPENDENCE o Person, place, object, event, concept (often corresponds to a row - Allows changes to be made in the in a table) conceptual schema without affecting the - Entity Type external schema. o Collection of entities (often - Modifications such as adding new entities, corresponds to a table) attributes, or relationships can occur without requiring changes in user views or RELATIONSHIP application logic. - Relationship instance Conceptual Logical Physical o Link between entities (corresponds ERD RDM DBMS to primary key-foreign key Entity Relation Table equivalencies in related tables) Attribute Cel Column/field - Relationship type Instance Tuple Row/record o Category of relationship…link Identifier Primary key / Primary key / between entity types foreign key foreign key - Attribute o Property or characteristic of an entity UNIT 2 or relationship type (often corresponds to a field in a table) BUSINESS RULES AND THE ENTITY RELATIONSHIP MODEL CONSTRUCTS IDENTIFIERS (KEYS) BUSINESS RULES - An attribute (or combination of attributes) that uniquely identifies individual instances - Statements that define or constraint some of an entity type aspect of the business - Simple versus Composite Identifier - Automated through DBMS software - Candidate Identifier A GOOD BUSINESS RULE IS o an attribute that could be a key…satisfies the requirements for 1. Declarative being an identifier a. What, not how 2. Precise RELATIONSHIP TYPES VS. RELATIONSHIP a. Clear, agreed-upon meaning INSTANCES 3. Atomic - Relationship types a. one statement o It represents how entities are related 4. Consistent to each other in a conceptual a. internally and externally manner 5. Expressible 4  INFORMATION MANAGEMENT | CC 106 | 2024 – 2025 | 1st Semester - Relationship instances - Dependent on a strong entity (identifying o It represents actual data in the owner) cannot exist on its own database - Does not have a unique identifier (only a partial identifier) ASSOCIATIVE ENTITY - Partial identifier underlined with double-line - Combination of relationship and entity - Entity box has double line DEGREE OF RELATIONSHIPS IDENTIFYING RELATIONSHIP - number of entity types that participate in it - Links strong entities to weak entities 1. Unary Relationship ASSOCIATIVE ENTITIES 2. Binary Relationship 3. Ternary Relationship ENTITY CARDINALITY OF RELATIONSHIPS - Has attributes 1. One to one RELATIONSHIP a. Each entity in the relationship will have exactly one related entity - Links entities together 2. One to many When should a relationship with attributes instead a. An entity on one side of the be an associative entity? relationship can have many related entities, but an entity on the other - All relationships for the associative entity side will have a maximum of one should be many related entity - The associative entity could have meant 3. Many to many independent of the other entities a. Entities on both sides of the - The associative entity preferably has a relationship can have many related unique identifier, and should also have other entities on the other side attributes - The associative entity may participate in CARDINALITY CONSTRAINTS other relationships other than the entities of - The number of instances of one entity that the associated relationship can or must be associated with each - Ternary relationships should be converted to instance of another entity associative entities 1. Minimum cardinality UNIT 3 a. If zero, then optional b. If one or more, then mandatory THE SUPERTYPE / SUBTYPE NOTATION 2. Maximum cardinality a. The maximum number SUBTYPE - A subgrouping of the entities in an entity STRONG VS. WEAK ENTITIES, AND IDENTIFYING RELATIONSHIPS type that has attributes distinct from those in other subgroupings STRONG ENTITY SUPERTYPE - Exist independently of other types of entities - Has its own unique identifier - A generic entity type that has a relationship - Identifier underlined with single-line with one or more subtypes WEAK ENTITY 5  INFORMATION MANAGEMENT | CC 106 | 2024 – 2025 | 1st Semester ATTRIBUTE INHERITANCE CONSTRAINTS IN SUPERTYPE / DISJOINTNESS CONSTRAINT - Subtype entities inherit values of all attributes of the supertype DISJOINTNESS CONSTRAINTS - An instance of a subtype is also an instance of the supertype - Whether an instance of a supertype may simultaneously be a member of two (or RELATIONSHIP AND SUBTYPES more) subtypes - Relationships at the supertype level indicate o Disjoint rule / d: that all subtypes will participate in the ▪ An instance of the supertype relationship can be only ONE of the - The instances of a subtype may participate subtypes in a relationship unique to that subtype. In o Overlap rule / o: this situation, the relationship is shown at ▪ An instance of the supertype the subtype level could be more than one of the subtypes GENERALIZATION AND SPECIALIZATION CONSTRAINTS IN SUPERTYPE / SUBTYPE GENERALIZATION DISCRIMINATORS - The process of defining a more general SUBTYPE DISCRIMINATOR entity type from a set of more specialized - Subtype discriminator is an attribute added entity types. BOTTOM-UP to the supertype whose values determine SPECIALIZATION which subtype a particular entity instance is connected. - The process of defining one or more o Disjoint subtypes of the supertype and forming ▪ a simple attribute with supertype/subtype relationships. TOP- alternative values to indicate DOWN the possible subtypes CONSTRAINTS IN SUPERTYPE / o Overlapping COMPLETENESS CONSTRAINTS ▪ a composite attribute whose subparts pertain to different COMPLETENESS CONSTRAINTS subtypes. - Whether an instance of a supertype must ▪ Each subpart contains a also be a member of at least one subtype boolean value to indicate o Total specialization rule: whether or not the instance ▪ Yes (double line) belongs to the associated ▪ Required to be part of a subtype subtype UNIT 4 o Partial specialization rule: ▪ No (single line) THE RELATIONAL DATABASE MODEL ▪ Not required to be part of a LOGICAL DATABASE DESIGN subtype - Logical database design is a process of transforming the conceptual data model (E- R model) into a logical data model that emphasizes the relational data model. 6  INFORMATION MANAGEMENT | CC 106 | 2024 – 2025 | 1st Semester - Relational data model represents data in - Rows correspond with entity instances and form of tables. with many-to-many relationship instances - Relational data model is based on - Columns correspond with attributes mathematical theory and therefore has a - NOTE: The word relation (in relational solid theoretical foundation. database) is NOT the same as the word relationship (in E-R model) 1. Data Structure RELATIONAL KEYS a. Data are organized in form of tables - Primary Key with rows and columns. o Unique identifiers of the relation in 2. Data Manipulation question. a. Powerful operations are used to o It consists of one or more columns manipulate data stored in the whose data contained within are relations. used to uniquely identify each row in 3. Data Integrity the table. a. Data validation ensured that the data o The primary key is designated by will be used is valid and determines underlining the column(s). the quality of the generated - Foreign Key information. o Are identifiers that enable a dependent relation (on the many RELATIONAL DATA STRUCTURE side of a relationship) to refer to its - A relation is named a two-dimensional table parent relation (on the one side of of data. the relationship) - Consists of rows (records) and columns o It is a set of one or more columns in (attribute or field) a table that refers to the primary key o Shorthand notation: TABLE_NAME in another table. (columnname_1, columnname_2) o The foreign key is designated by - Requirements for a table to qualify as a underlining the column name with a relation: dashed line. o It must have a unique name o Foreign keys are used to create o Every attribute value must be atomic relationships and provide a link (not multivalued, not composite) between tables. o Every row must be unique (can’t - Keys can be simple (a single field) or have two rows with exactly the same composite (more than one field) values for all their fields) - Keys usually are used as indexes to speed o Attributes (columns) in tables must up the response to user queries have unique names INTEGRITY CONSTRAINTS o The order of the columns must be irrelevant TYPES OF INTEGRITY CONSTRAINTS o The order of the rows must be irrelevant - Domain Constraints o Allowable values for an attribute. CORRESPONDENCE WITH E-R MODEL o A domain is the set of values that should be associated with the - Relations (tables) correspond with entity column. types and with many-to-many relationship types 7  INFORMATION MANAGEMENT | CC 106 | 2024 – 2025 | 1st Semester o We need to be specific on what data - Mapping Weak Entities values are and which format is o Becomes a separate relation with a suitable for a column. foreign key taken from the superior - Entity Integrity entity o No primary key attribute may be null. o Primary key composed of: All primary key fields MUST have ▪ Partial identifier of weak data. entity - Referential Integrity ▪ Primary key of identifying o rule states that any foreign key value relation (strong entity) (on the relation of the many side) - Mapping Binary Relationships MUST match a primary key value in o One-to-Many the relation of the one side. (Or the ▪ Primary key on the one side foreign key can be null) becomes a foreign key on o For example: Delete Rules the many side ▪ Restrict o Many-to-Many don’t allow delete of ▪ Create a new relation with “parent” side if related the primary keys of the two rows exist in entities as its primary key “dependent” side o One-to-One ▪ Cascade ▪ Primary key on the automatically delete mandatory side becomes a “dependent” side foreign key on the optional rows that correspond side with the “parent” side Foreign key goes in row to be deleted the relation on the ▪ Set-to-Null optional side, set the foreign key in Matching the primary the dependent side to key on the mandatory null if deleting from side the parent side → not - Mapping Associative Entities allowed for weak o Identifier Not Assigned entities ▪ Default primary key for the association relation is TRANSFORMING EER DIAGRAMS INTO composed of the primary RELATIONS keys of the two entities (as in - Mapping Regular Entities to Relations M:N relationship) o Simple attributes o Identifier Assigned ▪ E-R attributes map directly ▪ It is natural and familiar to onto the relation end-users o Composite attributes ▪ Default identifier may not be ▪ Use only their simple, unique component attributes - Mapping Unary Relationships o Multivalued attributes o One-to-Many ▪ Becomes a separate relation ▪ Recursive foreign key in the with a foreign key taken from same relation the superior entity o Many-to-Many-Two Relations: ▪ One for the entity type 8  INFORMATION MANAGEMENT | CC 106 | 2024 – 2025 | 1st Semester ▪ One for an associative TYPES OF ANOMALIES relation in which the primary 1. Insertion Anomaly key has two attributes, both a. If a row/record is inserted in taken from the primary key of referencing relation and referencing the entity attribute value is not present in - Mapping Ternary (and n-ary) referenced attribute, it will not allow Relationships insertion in referencing relation. o One relation for each entity and one 2. Deletion Anomaly for the associative entity a. Occurs when deleting a record from o Associative entity has foreign keys a database results in the to each entity in the relationship unintentional loss of other data. - Mapping Supertype/Subtype b. This can happen when a record Relationships contains attributes that shouldn't be o One relation for supertype and for deleted. each subtype 3. Modification Anomaly o Supertype attributes (including a. Occurs when data changes need to identifier and subtype discriminator) be updated in multiple places, but go into supertype relation aren't updated consistently. o Subtype attributes go into each subtype; primary key of supertype NORMAL FORMS relation also becomes primary key of subtype relation - First Normal Form: o 1:1 relationship established between o Remove all multivalued attributes supertype and each subtype, with - Second Normal Form: supertype as primary table o First Normal Form + Remove all partial functional dependencies UNIT 5 o Partial functional dependency ▪ refers to the attributes that DATA NORMALIZATION are partly dependent onto the - Refers to the process of structuring the primary key. database in order to improve the data - Third Normal Form: integrity. o Second Normal Form + Remove all - It is a tool used to improve the logical transitive dependencies design of the database in order to avoid o The non-key determinant becomes a unnecessary duplication of data. primary key in the new table, while, - It follows the process of decomposing the in the old table it stays a foreign key relations with anomalies to produce a well- o structured relation. WELL STRUCTURED RELATION - A relation containing minimal data redundancy. - Allows the users to insert, update, and delete data without causing any inconsistencies. 9  INFORMATION MANAGEMENT | CC 106 | 2024 – 2025 | 1st Semester FIRST NORMAL FORM SECOND NORMAL FORM THIRD NORMAL FORM 10 INFORMATION MANAGEMENT | CC 106 | 2024 – 2025 | 1st Semester 11