Information Management Module 1 PDF

INFORMATION MANAGEMENT Module 1: Database Environment and Development Process  MODULE 1 SUBTOPIC 1 THE DATABASE ENVIRONMENT At the end of the chapter, the learner should be able to: Define Terms. Name limitations of conventional file processing E...

INFORMATION MANAGEMENT Module 1: Database Environment and Development Process  MODULE 1 SUBTOPIC 1 THE DATABASE ENVIRONMENT At the end of the chapter, the learner should be able to: Define Terms. Name limitations of conventional file processing Explain advantages of databases Identify costs and risks of databases List components of database environment Identify categories of database applications  Database: organized collection of logically related data Data: stored representations of meaningful objects and events Structured: numbers, text, dates Unstructured: images, video, documents Information: data processed to increase knowledge in the person using the data Metadata: data that describes the properties and context of user data Context helps users understand data Graphical displays turn data into useful information that managers can use for decision making and interpretation  Descriptions of the properties or characteristics of the data, including data types, field sizes, allowable values, and data context Database Systems 1  Program-Data Dependence All programs maintain metadata for each file they use Database Systems 1  Duplication of Data Different systems/programs have separate copies of the same data Database Systems 1  Limited Data Sharing No centralized control of data Database Systems 1  Lengthy Development Times Programmers must design their own file formats Database Systems 1  Excessive Program Maintenance 80% of information systems budget Database Systems 1  Central repository of shared data Data is managed by a controlling agent Stored in a standardized, convenient form Requires a Database Management System (DBMS) Database Systems 1  A software system that is used to create, maintain, and provide controlled access to user databases Order Filing System Invoicing Central database DBMS System Contains employee, order, inventory, Payroll pricing, and System customer data Database Systems 1 1. Program-data independence 2. Planned data redundancy 3. Improved data consistency 4. Improved data sharing 5. Increased application development productivity Database Systems 1 6. Enforcement of standards 7. Improved data quality 8. Improved data accessibility and responsiveness 9. Reduced program maintenance 10.Improved decision support Database Systems 1 1. New, specialized personnel 2. Installation and management cost and complexity 3. Conversion costs 4. Need for explicit backup and recovery 5. Organizational conflict Data models – Graphical system capturing nature and relationship of data – Enterprise Data Model–high-level entities and relationships for the organization – Project Data Model–more detailed view, matching data structure in database or data warehouse Entities – Noun form describing a person, place, object, event, or concept – Composed of attributes Relationships – Between entities – Usually one-to-many (1:M) or many-to-many (M:N) Relational Databases – Database technology involving tables (relations) representing entities and primary/foreign keys representing relationships  CASE Tools–computer-aided software engineering Repository–centralized storehouse of metadata Database Management System (DBMS) –software for managing the database Database–storehouse of the data Application Programs–software using the data User Interface–text and graphical displays to users Data/Database Administrators–personnel responsible for maintaining the database System Developers–personnel responsible for designing databases and software End Users–people who use the applications and databases  Application Programs–software using the data User Interface–text and graphical displays to users Data/Database Administrators–personnel responsible for maintaining the database System Developers–personnel responsible for designing databases and software End Users–people who use the applications and databases END OF MODULE 1 SUBTOPIC 1  MODULE 1 SUBTOPIC 2 DATABASE DEVELOPMENT PROCESS At the end of the chapter, the learner should be able to: Describe database system development life cycle Explain prototyping and agile development approaches Explain roles of individuals Explain the three-schema architecture for databases SDLC –System Development Life Cycle –Detailed, well-planned development process –Time-consuming, but comprehensive –Long development cycle Prototyping –Rapid application development (RAD) –Cursory attempt at conceptual data modeling –Define database during development of initial prototype –Repeat implementation and maintenance activities with new prototype versions Database Systems 1  Planning Analysis Logical Design Physical Design Implementation Maintenance Database Systems 1  Planning Planning Purpose–preliminary understanding Deliverable–request for study Analysis Logical Design Physical Design Database activity– Implementation enterprise modeling and early conceptual Maintenance data modeling Database Systems 1  Purpose–thorough requirements analysis Planning and structuring Deliverable–functional system specifications Analysis Analysis Logical Design Physical Design Database activity–thorough Implementation and integrated conceptual data modeling Maintenance Database Systems 1  Purpose–information requirements elicitation Planning and structure Deliverable–detailed design specifications Analysis Logical Design Logical Design Physical Design Database activity– Implementation logical database design (transactions, forms, Maintenance displays, views, data integrity and security) Database Systems 1  Purpose–develop technology and Planning organizational specifications Analysis Deliverable–program/data structures, technology purchases, organization redesigns Logical Design Physical Design Physical Design Database activity– Implementation physical database design (define database to DBMS, Maintenance physical data organization, database processing programs) Database Systems 1  Purpose–programming, testing, Planning training, installation, documenting Analysis Deliverable–operational programs, documentation, training materials Logical Design Physical Design Database activity– Implementation Implementation database implementation, including coded programs, documentation, Maintenance installation and conversion Database Systems 1 Planning Purpose–monitor, repair, enhance Analysis Deliverable–periodic audits Logical Design Physical Design Database activity– Implementation database maintenance, performance analysis and tuning, error Maintenance Maintenance corrections Database Systems 1 External Schema User Views Subsets of Conceptual Schema Can be determined from business-function/data entity matrices DBA determines schema for different users Conceptual Schema E-R models Internal Schema Logical structures Physical structures Database Systems 1 Different people have different views of the database…these are the external schema The internal schema is the underlying design and implementation Database Systems 1  Project–a planned undertaking of related activities to reach an objective that has a beginning and an end Initiated and planned in planning stage of SDLC Executed during analysis, design, and implementation Closed at the end of implementation Database Systems 1  Business analysts Systems analysts Database analysts and data modelers Data/Database administrators Project managers Users Programmers Database architects Other technical experts Database Systems 1 Driven by four main objectives: Need for program-data independence ➔ reduced maintenance Desire to manage more complex data types and structures Ease of data access for less technical personnel Need for more powerful decision support platforms Database Systems 1 Database Systems 1 Database Systems 1 Database Systems 1 Database Systems 1 Database Systems 1 Database Systems 1 Database Systems 1 Database Systems 1  Personal databases Multitier client/server databases Enterprise applications Enterprise resource planning (ERP) systems Data warehousing implementations Database Systems 1 Database Systems 1 Database Systems 1  Taylor, A. G. (2019). SQL for dummies (9th ed.). Hoboken, NJ: For Dummies. Harrington, J. (2016). Relational Database Design and Implementation (4th Edition). Morgan Kaufmann Juric, N., Vrbsky, S., Nestorov, S. (2016). Database Systems: Introduction to Databases and Data Warehouses. Prospect Press Kroenke, D. M., & Auer, D. J. (2016). Database Concepts. Pearson. Sullivan, D. (2015). NoSQL for Mere Mortals (1st ed.). Boston: Addison- Wesley. Hoffer, J., Ramesh, V., Topi, H. (2016). Modern Database Management 12th Edition, Prentice Hall. END OF SUBTOPIC 2 END OF MODULE 1 INFORMATION MANAGEMENT MODULE 2: Modeling Data in the Organization  MODULE 2 SUBTOPIC 1 ENTITY RELATIONSHIP DIAGRAM(ERD)  OBJECTIVES At the end of the chapter, the learner should be able to: Define terms Understand importance of data modeling Write good names and definitions for entities, relationships, and attributes Distinguish unary, binary, and ternary relationships Model different types of attributes, entities, relationships, and cardinalities Draw E-R diagrams for common business situations Convert many-to-many relationships to associative entities Explanation of a term or fact Term–word or phrase with specific meaning Fact–association between two or more terms Guidelines for good data definition A concise description of essential data meaning Gathered in conjunction with systems requirements Accompanied by diagrams Achieved by consensus, and iteratively refined  Related to business, not technical, characteristics Meaningful and self-documenting Unique Readable Composed of words from an approved list Repeatable Written in standard syntax Entities: Entity instance–person, place, object, event, concept (often corresponds to a row in a table) Entity Type–collection of entities (often corresponds to a table) Relationships: Relationship instance–link between entities (corresponds to primary key- foreign key equivalencies in related tables) Relationship type–category of relationship…link between entity types Attributes: Properties or characteristics of an entity or relationship type (often corresponds to a field in a table)  Entity Attribute symbols symbols A special entity that is Relationship also a symbols relationship Relationship degrees specify Relationship number of cardinalities entity types specify how many involved of each entity type is allowed  Entities are represented by softboxes Entity names go in the softboxes Entity names are always singular and written in capital letters  Attributes are listed under entity names Mandatory attributes are marked with an asterisk: “*” Optional attributes are marked with a circle: “o” Unique identifiers are marked with a hash sign: “#”  Are statements that define or constrain some aspect of the business Are derived from policies, procedures, events, functions Assert business structure Control/influence business behavior Are expressed in terms familiar to end users Are automated through DBMS software  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  Entity – a person, a place, an object, an event, or a concept in the user environment about which the organization wishes to maintain data Entity type – a collection of entities that share common properties or characteristics Entity instance – A single occurrence of an entity type Attribute–property or characteristic of an entity or relationship type Entity instance EMPLOYEE ID First Name Last Name Hire Date Salary Commission 100 Santos Albert Jul-20-1990 45000 0.8 attributes 101 Rivera Sally Aug-23- 46000 0.5 1996 Entity Type  Entity – a person, a place, an object, an event, or a concept in the user environment about which the organization wishes to maintain data Entity type – a collection of entities that share common properties or characteristics Entity instance – A single occurrence of an entity type System System user Inappropriate output entities Appropriate entities Strong entity exists independently of other types of entities has its own unique identifier identifier underlined with single line Weak entity dependent on a strong entity (identifying owner)…cannot exist on its own does not have a unique identifier (only a partial identifier) entity box and partial identifier have double lines Identifying relationship links strong entities to weak entities Strong entity Weak entity Attribute–property or characteristic of an entity or relationship type  Name should be a singular noun or noun phrase Name should be unique Name should follow a standard format e.g. [Entity type name { [ Qualifier ] } ] Class Similar attributes of different entity types should use the same qualifiers and classes  State what the attribute is and possibly why it is important Make it clear what is and is not included in the attribute’s value Include aliases in documentation State source of values Specify required vs. optional State min and max number of occurrences allowed Indicate relationships with other attributes  Required versus Optional Attributes Simple versus Composite Attribute Single-Valued versus Multivalued Attribute Stored versus Derived Attributes Identifier Attributes Required – must have a value for every entity (or Optional – may not have a value for every entity (or relationship) instance with which it is associated relationship) instance with which it is associated Composite attribute – An attribute that has meaningful component parts (attributes) The address is broken into component parts Figure 2-7 A composite attribute Multivalued – may take on more than Derived – values can be calculated from one value for a given entity (or related attribute values (not physically relationship) instance stored in the database) Figure 2-8 Entity with multivalued attribute (Skill) and derived attribute (Years Employed) Multivalued Derived an employee can Calculated have more than one from date skill employed and current date  Identifier (Key)–an attribute (or combination of attributes) that uniquely identifies individual instances of an entity type Simple versus Composite Identifier Candidate Identifier–an attribute that could be a key…satisfies the requirements for being an identifier  Choose Identifiers that Will not change in value Will not be null Avoid intelligent identifiers (e.g., containing locations or people that might change) Substitute new, simple keys for long, composite keys The identifier is boldfaced and underlined  Relationship Types vs. Relationship Instances The relationship type is modeled as lines between entity types…the instance is between specific entity instances Relationships can have attributes These describe features pertaining to the association between the entities in the relationship Two entities can have more than one type of relationship between them (multiple relationships) Associative Entity–combination of relationship and entity a) Relationship type (Completes) b) Relationship instances  Degree of a relationship is the number of entity types that participate in it Unary Relationship Binary Relationship Ternary Relationship  Entities of One entity two related to different Entities of three another of different types types related related to each the same to each other entity type other a) Unary relationships b) Binary relationships c) Ternary relationship Note: a relationship can have attributes of its own One-to-One Each entity in the relationship will have exactly one related entity 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 Many-to-Many Entities on both sides of the relationship can have many related entities on the other side  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 Maximum Cardinality The maximum number a) Mandatory cardinalities A patient history is A patient must have recorded for one and recorded at least one only one patient history, and can have many b) One optional, one mandatory A project must be assigned to at An employee can be assigned to least one employee, and may be any number of projects, or may not assigned to many be assigned to any at all c) Optional cardinalities A person is married to at most one other person, or may not be married at all a) Employees and departments Entities can be related to one another in more than one way b) Professors and courses (fixed lower limit constraint) Here, min cardinality constraint is 2. At least two professors must be qualified to teach each course. Each professor must be qualified to teach at least one course. simple composite An entity–has attributes A relationship–links entities together When should a relationship with attributes instead be an associative entity? All relationships for the associative entity should be many The associative entity could have meaning independent of the other entities The associative entity preferably has a unique identifier, and should also have other attributes The associative entity may participate in other relationships other than the entities of the associated relationship Ternary relationships should be converted to associative entities Associative entity is like a relationship with an attribute, but it is also considered to be an entity in its own right. Note that the many-to-many cardinality between entities in Figure 2-11a has been replaced by two one-to-many relationships with the associative entity. a) Relationship type (Completes)  ERD example Business Rules: A library system contains books, authors and patrons, with attributes book number, author number and patron number, respectively. Books are further described by title and page count Authors by author name, and Patrons by patron name. Books should have at least one author or can have more. An author can author a book or many books Patrons borrow books but at any point in time, may not have anything checked out. When they do have a book checked out, there is a due date associated with it. Figure 2-22 Data model for Pine Valley Furniture Company in Microsoft Visio notation Different modeling software tools may have different notation for the same constructs. In this lesson, you should have learned the following: Importance of data modeling Unary, binary, and ternary relationships Model different types of attributes, entities, relationships, and cardinalities Draw E-R diagrams for common business situations Convert many-to-many relationships to associative entities  Taylor, A. G. (2019). SQL for dummies (9th ed.). Hoboken, NJ: For Dummies. Harrington, J. (2016). Relational Database Design and Implementation (4th Edition). Morgan Kaufmann Juric, N., Vrbsky, S., Nestorov, S. (2016). Database Systems: Introduction to Databases and Data Warehouses. Prospect Press Kroenke, D. M., & Auer, D. J. (2016). Database Concepts. Pearson. Sullivan, D. (2015). NoSQL for Mere Mortals (1st ed.). Boston: Addison-Wesley. Hoffer, J., Ramesh, V., Topi, H. (2013). Modern Database Management 11th Edition, Prentice Hall. ASK ANY QUESTION RELATED TO OUR TOPIC FOR TODAY. INFORMATION MANAGEMENT MODULE 3: Enhanced ER Model  MODULE 3 SUBTOPIC 3 THE ENHANCED E-R MODEL  MODULE 3 OBJECTIVES At the end of the chapter, the learner should be able to: Define terms Understand use of supertype/subtype relationships Understand use of specialization and generalization techniques Specify completeness and disjointness constraints Develop supertype/subtype hierarchies for realistic business situations Develop entity clusters  Enhanced ER model: extends original ER model with new modeling constructs Subtype: A subgrouping of the entities in an entity type that has attributes distinct from those in other subgroupings Supertype: A generic entity type that has a relationship with one or more subtypes Attribute Inheritance: Subtype entities inherit values of all attributes of the supertype An instance of a subtype is also an instance of the supertype a) EER notation  b) Microsoft Visio Notation Different modeling tools may have different notation for the same modeling constructs.  Relationships at the supertype level indicate that all subtypes will participate in the relationship The instances of a subtype may participate in a relationship unique to that subtype. In this situation, the relationship is shown at the subtype level All employee subtypes will have employee number, name, address, and date hired Each employee subtype will also have its own attributes  Generalization: The process of defining a more general entity type from a set of more specialized entity types. BOTTOM-UP Specialization: The process of defining one or more subtypes of the supertype and forming supertype/subtype relationships. TOP- DOWN  Figure 3-4 Example of generalization a) Three entity types: CAR, TRUCK, and MOTORCYCLE All these types of vehicles have common attributes  Figure 3-4 Example of generalization (cont.) b) Generalization to VEHICLE supertype So we put the shared attributes in a supertype Note: no subtype for motorcycle, since it has no unique attributes Figure 3-5 Example of specialization a) Entity type PART Only applies to manufactured parts Applies only to purchased parts  Figure 3-5 Example of specialization (cont.) b) Specialization to MANUFACTURED PART and PURCHASED PART Created 2 subtypes Note: multivalued composite attribute was replaced by an associative entity relationship to another entity Completeness Constraints: Whether an instance of a supertype must also be a member of at least one subtype Total Specialization Rule: Yes (double line) - A rule that specifies that each entity instance of a supertype must be a member of some subtype in the relationship. Partial Specialization Rule: No (single line) - A rule that specifies that an entity instance of a supertype is allowed not to belong to any subtype. Figure 3-6 Examples of completeness constraints a) Total specialization rule Figure 3-6 Examples of completeness constraints (cont.) b) Partial specialization rule  Disjointness Constraints: Whether an instance of a supertype may simultaneously be a member of two (or more) subtypes – Disjoint Rule: An instance of the supertype can be only ONE of the subtypes – Overlap Rule: An instance of the supertype could be more than one of the subtypes Figure 3-7 Examples of disjointness constraints a) Disjoint rule Figure 3-7 Examples of disjointness constraints (cont.) b) Overlap rule Subtype Discriminator: An attribute of the supertype whose values determine the target subtype(s) Disjoint – a simple attribute with alternative values to indicate the possible subtypes Overlapping – a composite attribute whose subparts pertain to different subtypes. Each subpart contains a Boolean value to indicate whether or not the instance belongs to the associated subtype Figure 3-8 Introducing a subtype discriminator (disjoint rule) Figure 3-9 Subtype discriminator (overlap rule) When a new instance is added to PART, these components are coded as follows: Figure 3-10 Example of supertype/subtype hierarchy  EER diagrams are difficult to read when there are too many entities and relationships. Solution: Group entities and relationships into entity clusters. Entity cluster: Set of one or more entity types and associated relationships grouped into a single abstract entity type Figure 3-13a Possible entity clusters for Pine Valley Furniture in Microsoft Visio Related groups of entities could become clusters Figure 3-13b EER diagram of PVF entity clusters More readable, isn’t it? Figure 3-14 Manufacturing entity cluster Detail for a single cluster In this lesson, you should have learned the following: Use of supertype/subtype relationships Use of specialization and generalization techniques Specify completeness and disjointness constraints Develop supertype/subtype hierarchies for realistic business situations Develop entity clusters  Taylor, A. G. (2019). SQL for dummies (9th ed.). Hoboken, NJ: For Dummies. Harrington, J. (2016). Relational Database Design and Implementation (4th Edition). Morgan Kaufmann Juric, N., Vrbsky, S., Nestorov, S. (2016). Database Systems: Introduction to Databases and Data Warehouses. Prospect Press Kroenke, D. M., & Auer, D. J. (2016). Database Concepts. Pearson. Sullivan, D. (2015). NoSQL for Mere Mortals (1st ed.). Boston: Addison-Wesley. Hoffer, J., Ramesh, V., Topi, H. (2013). Modern Database Management 11th Edition, Prentice Hall. ASK ANY QUESTION RELATED TO OUR TOPIC FOR TODAY. INFORMATION MANAGEMENT MODULE 4: Relational Database Design and The Relational Model  MODULE 4 SUBTOPIC 1 LOGICAL DATABASE DESIGN  MODULE 4 OBJECTIVES At the end of the chapter, the learner should be able to: Define terms List five properties of relations State two properties of candidate keys Define first, second, and third normal form Transform E-R and EER diagrams to relations Create tables with entity and relational integrity constraints Use normalization to convert anomalous tables to well-structured relations The relational data model represents data in the form of tables. Data structure Tables (relations), rows, columns Data manipulation Powerful SQL operations for retrieving and modifying data Data integrity Mechanisms for implementing business rules that maintain integrity of manipulated data  A relation is a named, two-dimensional table of data. A table consists of rows (records) and columns (attribute or field). Figure 4-1  We must be able to store and retrieve a row of data in a relation, based on the data values stored in that row. Goal: every relation must have primary keys A primary key is an attribute or a combination of attributes that uniquely identifies each row in a relation We designate a primary key by underlining the attribute name(s). For example, the primary key for the relation EMPLOYEE1 is EmpID. Notice that this attribute is underlined in Figure 4-1. In shorthand notation, we express this relation as follows:  A foreign key is an attribute (possibly composite) in a relation that serves as the primary key of another relation. For example, consider the relations EMPLOYEE1 and DEPARTMENT:  Requirements for a table to qualify as a relation: It must have a unique name. Every attribute value must be atomic (not multivalued, not composite). Every row must be unique (can’t have two rows with exactly the same values for all their fields). Attributes (columns) in tables must have unique names. The order of the columns must be irrelevant. The order of the rows must be irrelevant. NOTE: All relations are in 1st Normal form.  Relations (tables) correspond with entity types and with many-to-many relationship types. Rows correspond with entity instances and with many-to- many relationship instances. Columns correspond with attributes. NOTE: The word relation (in relational database) is NOT the same as the word relationship (in E-R model).  The second property of relations listed in the preceding section states that no multivalued attributes are allowed in a relation. Thus, a table that contains one or more multivalued attributes is not a relation. Figure 4-3 Schema for four relations (Pine Valley Furniture Company) Primary Key Foreign Key (implements 1:N relationship between customer and order) Combined, these are a composite primary key (uniquely identifies the order line)…individually they are foreign keys (implement M:N relationship between order and product) 1. Domain Constraints Allowable values for an attribute (See Table 4-1). A domain definition usually consists of the following components: domain name, meaning, data type, size (or length), and allowable values or allowable range (if applicable). 2. Entity Integrity The relational data model allows us to assign a null value to an attribute in the just described situations. A null is a value that may be assigned to an attribute when no other value applies or when the applicable value is unknown. No primary key attribute may be null. All primary key fields MUST have data. 3. Referential Integrity 3. Referential Integrity–rule states that any foreign key value (on the relation of the many side) MUST match a primary key value in the relation of the one side. (Or the foreign key can Figure 4-5 be null) Referential integrity constraints (Pine Valley Furniture) Referential integrity constraints are drawn via arrows from dependent to parent table 3. Referential Integrity– For example: Delete Rules Restrict–don’t allow delete of “parent” side if related rows exist in “dependent” side Cascade–automatically delete “dependent” side rows that correspond with the “parent” side row to be deleted Set-to-Null–set the foreign key in the dependent side to null if deleting from the parent side → not allowed for weak entities Figure 4-6 SQL table definitions Referential integrity constraints are implemented with foreign key to primary key references. Mapping Regular Entities to Relations Simple attributes: E-R attributes map directly onto the relation Composite attributes: Use only their simple, component attributes Multivalued Attribute: Becomes a separate relation with a foreign key taken from the superior entity  Figure 4-8 Mapping a regular entity (a) CUSTOMER entity type with simple attributes (b) CUSTOMER relation  Figure 4-9 Mapping a composite attribute (a) CUSTOMER entity type with composite attribute (b) CUSTOMER relation with address detail  Figure 4-10 Mapping an entity with a multivalued attribute (a) Multivalued attribute becomes a separate relation with foreign key (b) One–to–many relationship between original entity and new relation Mapping Weak Entities Becomes a separate relation with a foreign key taken from the superior entity Primary key composed of: Partial identifier of weak entity Primary key of identifying relation (strong entity) Figure 4-11 Example of mapping a weak entity Figure 4-11 Example of mapping a weak entity (cont.) a) Weak entity DEPENDENT b) Relations resulting from weak entity NOTE: the domain constraint for the foreign key should NOT allow null value if DEPENDENT is a weak Foreign key entity Composite primary key Relation Dependent Mapping Binary Relationships One-to-Many–Primary key on the one side becomes a foreign key on the many side Many-to-Many–Create a new relation with the primary keys of the two entities as its primary key One-to-One–Primary key on mandatory side becomes a foreign key on optional side  Figure 4-12 Example of mapping a 1:M relationship a) Relationship between customers and orders Note the mandatory one b) Mapping the relationship Again, no null value in the foreign key…this is because of the mandatory minimum cardinality. Foreign key  Figure 4-13 Example of mapping an M:N relationship a) Completes relationship (M:N) The Completes relationship will need to become a separate relation.  Figure 4-13 Example of mapping an M:N relationship (cont.) b) Three resulting relations Composite primary key Foreign key new Foreign key intersection relation  Figure 4-14 Example of mapping a binary 1:1 relationship a) In charge relationship (1:1) Often in 1:1 relationships, one direction is optional Figure 4-14 Example of mapping a binary 1:1 relationship (cont.) b) Resulting relations Foreign key goes in the relation on the optional side, matching the primary key on the mandatory side Mapping Associative Entities Identifier Not Assigned Default primary key for the association relation is composed of the primary keys of the two entities (as in M:N relationship) Identifier Assigned It is natural and familiar to end-users Default identifier may not be unique  Figure 4-15 Example of mapping an associative entity a) An associative entity Figure 4-15 Example of mapping an associative entity (cont.) b) Three resulting relations Composite primary key formed from the two foreign keys Figure 4-16 Example of mapping an associative entity with an identifier a) SHIPMENT associative entity  Figure 4-16 Example of mapping an associative entity with an identifier (cont.) b) Three resulting relations Primary key differs from foreign keys Mapping Unary Relationships One-to-Many–Recursive foreign key in the same relation Many-to-Many–Two relations: One for the entity type One for an associative relation in which the primary key has two attributes, both taken from the primary key of the entity  Figure 4-17 Mapping a unary 1:N relationship (a) EMPLOYEE entity with unary relationship (b) EMPLOYEE relation with recursive foreign key Figure 4-18 Mapping a unary M:N relationship (a) Bill-of-materials relationships (M:N) (b) ITEM and COMPONENT relations Mapping Ternary (and n-ary) Relationships One relation for each entity and one for the associative entity Associative entity has foreign keys to each entity in the relationship  Figure 4-19 Mapping a ternary relationship a) PATIENT TREATMENT Ternary relationship with associative entity  Figure 4-19 Mapping a ternary relationship (cont.) b) Mapping the ternary relationship PATIENT TREATMENT Remember that This is why treatment But this makes a very It would be better to the primary key date and time are cumbersome key… create a surrogate MUST be included in the key like Treatment#. unique. composite primary key. Mapping Supertype/Subtype Relationships One relation for supertype and for each subtype Supertype attributes (including identifier and subtype discriminator) go into supertype relation Subtype attributes go into each subtype; primary key of supertype relation also becomes primary key of subtype relation 1:1 relationship established between supertype and each subtype, with supertype as primary table Figure 4-20 Supertype/subtype relationships Figure 4-21 Mapping supertype/subtype relationships to relations These are implemented as one-to-one relationships.  MODULE 4 SUBTOPIC 1 NORMALIZATION  MODULE 4 OBJECTIVES At the end of the chapter, the learner should be able to: Define terms List five properties of relations State two properties of candidate keys Define first, second, and third normal form Transform E-R and EER diagrams to relations Create tables with entity and relational integrity constraints Use normalization to convert anomalous tables to well-structured relations  Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data The process of decomposing relations with anomalies to produce smaller, well-structured relations  A relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies Goal is to avoid anomalies Figure 4-1  Example–Figure 4-2b Question–Is this a relation? Answer–Yes: Unique rows and no multivalued attributes Question–What’s the primary key? Answer–Composite: EmpID, CourseTitle  Types of Anomalies: Insertion Anomaly–adding new rows forces user to create duplicate data Deletion Anomaly–deleting rows may cause a loss of data that would be needed for other future rows Modification Anomaly–changing data in a row forces changes to other rows because of duplication General rule of thumb: A table should not pertain to more than one entity type. Why do these anomalies exist? Because there are two themes (entity types) in this one relation. This results in data duplication and an unnecessary dependency between the entities. Figure 4.22 Steps in normalization 3rd normal form is generally considered sufficient  For example, consider the relation EMP COURSE (EmpID, CourseTitle, DateCompleted) shown in Figure 4-7. We represent the functional dependency in this relation as follows: The comma between EmpID and CourseTitle stands for the logical AND operator, because DateCompleted is functionally dependent on EmpID and CourseTitle in combination. The functional dependency in this statement implies that the date when a course is completed is determined by the identity of the employee and the title of the course. Typical examples of functional dependencies are the following: 1. SSN → Name, Address, Birthdate A person’s name, address, and birth date are functionally dependent on that person’s Social Security number (in other words, there can be only one Name, one Address, and one Birthdate for each SSN). 2. VIN → Make, Model, Color The make, model, and the original color of a vehicle are functionally dependent on the vehicle identification number (as above, there can be only one value of Make, Model, and Color associated with each VIN). 3. ISBN → Title, FirstAuthorName, Publisher The title of a book, the name of the first author, and the publisher are functionally dependent on the book’s international standard book number (ISBN).  The attribute on the left side of the arrow in a functional dependencyis called a determinant. SSN, VIN, and ISBN are determinants in the preceding three examples. In the EMP COURSE relation (Figure 4-7), the combination of EmpID and CourseTitle is a determinant.  Candidate key is an attribute, or combination of attributes, that uniquely identifies a row in a relation. A candidate key must satisfy the following properties ,which are a subset of the six properties of a relation previously listed: 1. Unique identification For every row, the value of the key must uniquely identify that row. This property implies that each nonkey attribute is functionally dependent on that key. 2. Nonredundancy No attribute in the key can be deleted without destroying the property of unique identification. STEPS IN NORMALIZATION  No multivalued attributes Every attribute value is atomic Fig. 4-25 is not in 1st Normal Form (multivalued attributes) ➔ it is not a relation. Fig. 4-26 is in 1st Normal form. All relations are in 1st Normal Form. Table with multivalued attributes, not in 1st normal form Note: This is NOT a relation. Table with multivalued attributes, not in 1st normal form Note: This is NOT a relation.  Insertion –if new product is ordered for order 1007 of existing customer, customer data must be re-entered, causing duplication Deletion –if we delete the Dining Table from Order 1006, we lose information concerning this item’s finish and price Update –changing the price of product ID 4 requires update in multiple records Why do these anomalies exist? Because there are multiple themes (entity types) in one relation. This results in duplication and an unnecessary dependency between the entities. four determinants in INVOICE 1NF PLUS every non-key attribute is fully functionally dependent on the ENTIRE primary key Every non-key attribute must be defined by the entire key, not by only part of the key No partial functional dependencies  Figure 4-27 Functional dependency diagram for INVOICE OrderID ➔ OrderDate, CustomerID, CustomerName, CustomerAddress CustomerID ➔ CustomerName, CustomerAddress ProductID ➔ ProductDescription, ProductFinish, ProductStandardPrice OrderID, ProductID ➔ OrderQuantity Therefore, NOT in 2nd Normal Form Figure 4-28 Removing partial dependencies Getting it into Second Normal Form Partial dependencies are removed, but there are still transitive dependencies  2NF PLUS no transitive dependencies (functional dependencies on non- primary-key attributes) Note: This is called transitive, because the primary key is a determinant for another attribute, which in turn is a determinant for a third Solution: Non-key determinant with transitive dependencies go into a new table; non-key determinant becomes primary key in the new table and stays as foreign key in the old table Figure 4-29 Removing partial dependencies Getting it into Third Normal Form Transitive dependencies are removed. In this lesson, you should have learned the following: Five properties of relations Two properties of candidate keys First, second, and third normal form Transform E-R and EER diagrams to relations Create tables with entity and relational integrity constraints Use normalization to convert anomalous tables to well-structured relations ASK ANY QUESTION RELATED TO OUR TOPIC FOR TODAY.  Taylor, A. G. (2019). SQL for dummies (9th ed.). Hoboken, NJ: For Dummies. Harrington, J. (2016). Relational Database Design and Implementation (4th Edition). Morgan Kaufmann Juric, N., Vrbsky, S., Nestorov, S. (2016). Database Systems: Introduction to Databases and Data Warehouses. Prospect Press Kroenke, D. M., & Auer, D. J. (2016). Database Concepts. Pearson. Sullivan, D. (2015). NoSQL for Mere Mortals (1st ed.). Boston: Addison- Wesley. Hoffer, J., Ramesh, V., Topi, H. (2013). Modern Database Management 11th Edition, Prentice Hall. INFORMATION MANAGEMENT MODULE 5: Introduction to SQL  MODULE 5 INTRODUCTION TO SQL At the end of the chapter, the learner should be able to: Define terms Interpret history and role of SQL Discuss SQL:1999 and SQL:2011 standards Identify the different types of SQL commands Explore the SQL Developer and SQL *Plus environment  Structured Query Language The standard for relational database management systems (RDBMS) RDBMS: A database management system that manages data as a collection of tables in which all relationships are represented by common values in related tables  1970–Edgar F. Codd develops relational database concept 1974-1979–System R with Sequel (later SQL) created at IBM Research Lab 1979–Oracle markets first relational DB with SQL 1981 – SQL/DS first available RDBMS system on DOS/VSE Others followed: INGRES (1981), IDM (1982), DG/SGL (1984), Sybase (1986) 1986–ANSI SQL standard released 1989, 1992, 1999, 2003, 2006, 2008, 2011–Major ANSI standard updates Current–SQL is supported by most major database vendors  Specify syntax/semantics for data definition and manipulation Define data structures and basic operations Enable portability of database definition and application modules Specify minimal (level 1) and complete (level 2) standards Allow for later growth/enhancement to standard (referential integrity, transaction management, user-defined functions, extended join operations, national character sets)  Reduced training costs Productivity Application portability Application longevity Reduced dependence on a single vendor Cross-system communication Catalog A set of schemas that constitute the description of a database Schema The structure that contains descriptions of objects created by a user (base tables, views, constraints) Data Definition Language (DDL) Commands that define a database, including creating, altering, and dropping tables and establishing constraints Data Manipulation Language (DML) Commands that maintain and query a database Data Control Language (DCL) Commands that control a database, including administering privileges and committing data  COMMANDS SQL LANGUAGE SELECT DATA MANIPULATION LANGUAGE (DML) INSERT UPDATE DELETE MERGE CREATE DATA DEFIINITION LANGUAGE (DDL) ALTER DROP RENAME TRUNCATE COMMENT GRANT DATA CONTROL LANGUAGE (DCL) REVOKE COMMIT TRANSACTION CONTROL ROLLBACK SAVEPOINT Figure 6-4 DDL, DML, DCL, and the database development process Manageability High availability Performance Security Information integration There are two development environments for this course: Primary tool is Oracle SQL Developer SQL*Plus command line interface may also be used SQL *Plus SQL Developer  1 Connections Database tabbed page Connection Window 2 3 Click to test Use the Connections Navigator to: Browse through many objects in a database schema Review the definitions of objects at a glance  Use the SQL Worksheet to enter and execute SQL, PL/SQL, and SQL *Plus statements. Specify any actions that can be processed by the database connection associated with the worksheet. Click the Open SQL Worksheet icon. Select SQL Worksheet from the Tools menu, or  2 4 6 8 1. Execute statement 2. Run script 1 3 5 7 9 3. Commit 4. Rollback 5. Cancel 6. SQL History 7. Execute explain plan 8. Autotrace 9. Clear  Use the SQL Worksheet to enter and execute SQL, PL/SQL, and SQL*Plus statements. Specify any actions that can be processed by the database connection associated with the worksheet. Enter SQL statements. Results are shown here. Use the Enter SQL Statement box to enter single or multiple SQL statements. Use the Enter SQL Statement box to enter single or multiple SQL statements. View the results on the Script Output tabbed page.  Click the Save icon to save your Enter a file name and identify a SQL statement to a file. location to save the file, and click Save. The contents of the saved file are visible and editable in your SQL Worksheet window. Use the Enter SQL Statement box to enter single or multiple SQL statements. F9 F5 F5 F9 Before formatting After formatting Snippets are code fragments that may be just syntax or examples. When you place your cursor here, it shows the Snippets window. From the drop-down list, you can select the functions category that you want.  1 sqlplus [username[/password[@database]]] 2 Use the SQL*Plus DESCRIBE command to display the structure of a table: DESC[RIBE] tablename DESCRIBE departments Name Null? Type ----------------------- -------- ------------ DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)  SAVE filename GET filename START filename @ filename EDIT filename SPOOL filename EXIT LIST 1 SELECT last_name, manager_id, department_id 2* FROM employees SAVE my_query Created file my_query START my_query LAST_NAME MANAGER_ID DEPARTMENT_ID ------------------------- ---------- ------------- King 90 Kochhar 100 90... 107 rows selected. EDIT my_query In this lesson, you should have learned the following: History of SQL SQL statements Browse, create, and edit database objects Execute, edit, and save SQL statements ASK ANY QUESTION RELATED TO OUR TOPIC FOR TODAY. Taylor, A. G. (2019). SQL for dummies (9th ed.). Hoboken, NJ: For Dummies. Harrington, J. (2016). Relational Database Design and Implementation (4th Edition). Morgan Kaufmann Juric, N., Vrbsky, S., Nestorov, S. (2016). Database Systems: Introduction to Databases and Data Warehouses. Prospect Press Kroenke, D. M., & Auer, D. J. (2016). Database Concepts. Pearson. Sullivan, D. (2015). NoSQL for Mere Mortals (1st ed.). Boston: Addison- Wesley. Hoffer, J., Ramesh, V., Topi, H. (2013). Modern Database Management 11th Edition, Prentice Hall INFORMATION MANAGEMENT MODULE 6: Data Definition Language (DDL)  MODULE 6 DATA DEFINITION LANGUAGE (DDL)  MODULE 6 OBJECTIVES At the end of the chapter, the learner should be able to: Define terms Define a database using SQL data definition language Establish referential integrity using SQL Use SQL commands to manage tables Data Definition Language (DDL) CREATE ALTER DROP RENAME TRUNCATE COMMENT Table Basic unit of storage; composed of rows Database Systems 1 Table names and column names: Must begin with a letter Must be 1–30 characters long Must contain only A–Z, a–z, 0–9, _, $, and # Must not duplicate the name of another object owned by the same user Must not be an Oracle server–reserved word Database Systems 1 Data Type Description VARCHAR2(size) Variable-length character data CHAR(size) Fixed-length character data NUMBER(p,s) Variable-length numeric data DATE Date and time values LONG Variable-length character data (up to 2 GB) CLOB Character data (up to 4 GB) RAW and LONG Raw binary data RAW BLOB Binary data (up to 4 GB) BFILE Binary data stored in an external file (up to 4 GB) ROWID A base-64 number system representing the unique address of a row in its table 1. Identify data types for attributes 2. Identify columns that can and cannot be null 3. Identify columns that must be unique (candidate keys) 4. Identify primary key–foreign key mates 5. Determine default values 6. Identify constraints on columns (domain specifications) 7. Create the table Database Systems 1  Constraints enforce rules at the table level. Constraints prevent the deletion of a table if there are dependencies. The following constraint types are valid: o NOT NULL o UNIQUE o PRIMARY KEY o FOREIGN KEY o CHECK Database Systems 1  You can name a constraint, or the Oracle server generates a name by using the SYS_Cn format. Create a constraint at either of the following times: At the same time as the creation of the table After the creation of the table Define a constraint at the column or table level. View a constraint in the data dictionary. Database Systems 1  CREATE TABLE [schema.]table Syntax: (column datatype [DEFAULT expr] [column_constraint],... [table_constraint][,...]); Column-level constraint syntax: column [CONSTRAINT constraint_name] constraint_type, Table-level constraint syntax: column,... [CONSTRAINT constraint_name] constraint_type (column,...), Database Systems 1 Database Systems 1 Figure 6-6 SQL database definition commands for Pine Valley Furniture Company (Oracle 11g) Overall table definitions  Non-nullable specification Identifying primary key  Non-nullable specifications Primary key Some primary keys are composite– composed of multiple attributes  Controlling the values in attributes Default value Domain constraint Identifying foreign keys and establishing relationships Primary key of parent table Foreign key of dependent table Referential integrity–constraint that ensures that foreign key values of a table must match primary key values of a related table in 1:M relationships Restricting: Deletes of primary records Updates of primary records Inserts of dependent records Database Systems 1  FOREIGN KEY: Defines the column in the child table at the table- constraint level REFERENCES: Identifies the table and column in the parent table ON DELETE CASCADE: Deletes the dependent rows in the child table when a row in the parent table is deleted ON DELETE SET NULL: Converts dependent foreign key values to null Database Systems 1 Figure 6-7 Ensuring data integrity through updates Relational integrity is enforced via the primary-key to foreign-key match ALTER TABLE statement allows you to change column specifications: Table Actions: Example (adding a new column with a default value): Database Systems 1 Use the ALTER TABLE statement to: Add or drop a constraint, but not modify its structure ALTER TABLE ADD [CONSTRAINT ] type (); ALTER TABLE emp2 ADD CONSTRAINT emp_mgr_fk FOREIGN KEY(manager_id) REFERENCES emp2(employee_id); Database Systems 1 RENAME COLUMN statement allows you to rename an existing column in an existing table in any schema. ALTER TABLE table-name RENAME COLUMN old-column-name to new- column-name; ALTER TABLE employee RENAME COLUMN manager to supervisor; Database Systems 1 You can use the ALTER TABLE syntax to: Put a table into read-only mode, which prevents DDL or DML changes during table maintenance Put the table back into read/write mode ALTER TABLE employees READ ONLY; -- perform table maintenance and then -- return table back to read/write mode ALTER TABLE employees READ WRITE; Database Systems 1  DROP TABLE statement allows you to remove tables from your schema. Moves a table to the recycle bin Removes the table and all its data entirely if the PURGE clause is specified DROP TABLE table_name [PURGE]; DROP TABLE CUSTOMER_T; Database Systems 1  Enables you to recover tables to a specified point in time with a single statement Restores table data along with associated indexes, and constraints Enables you to revert the table and its contents to a certain point in time or SCN SCN Database Systems 1  Repair tool for accidental table modifications Restores a table to an earlier point in time Benefits: Ease of use, availability, and fast execution Is performed in place Syntax: FLASHBACK TABLE[schema.]table[, [ schema.]table ]... TO { TIMESTAMP | SCN } expr [ { ENABLE | DISABLE } TRIGGERS ]; Database Systems 1 DROP TABLE emp2; SELECT original_name, operation, droptime FROM recyclebin; … FLASHBACK TABLE emp2 TO BEFORE DROP;  RENAME TABLE allows you to rename an existing table in any schema (except the schema SYS). To rename a table, you must either be the database owner or the table owner. RENAME table-name to new- table-name; RENAME employees to emp; Database Systems 1  Removes all rows from a table, leaving the table empty and the table structure intact Is a data definition language (DDL) statement rather than a DML statement; cannot easily be undone Syntax: TRUNCATE TABLE table_name; Example: TRUNCATE TABLE copy_emp; Database Systems 1  You can add comments to a table or column by using the COMMENT statement: COMMENT ON TABLE employees IS 'Employee Information'; COMMENT ON COLUMN employees.first_name IS 'First name of the employee'; Database Systems 1 In this lesson, you should have learned the following: Define a database using SQL data definition language Establish referential integrity using SQL Use SQL commands to manage tables END OF MODULE 6  Taylor, A. G. (2019). SQL for dummies (9th ed.). Hoboken, NJ: For Dummies. Harrington, J. (2016). Relational Database Design and Implementation (4th Edition). Morgan Kaufmann Juric, N., Vrbsky, S., Nestorov, S. (2016). Database Systems: Introduction to Databases and Data Warehouses. Prospect Press Kroenke, D. M., & Auer, D. J. (2016). Database Concepts. Pearson. Sullivan, D. (2015). NoSQL for Mere Mortals (1st ed.). Boston: Addison- Wesley. Hoffer, J., Ramesh, V., Topi, H. (2013). Modern Database Management 11th Edition, Prentice Hall. INFORMATION MANAGEMENT MODULE 7: Data Manipulation Language (DML) and Transaction Control  MODULE 7 SUBTOPIC 1 Overview of DML At the end of the chapter, the learner should be able to: Define terms Describe each data manipulation language (DML) statement Insert, update, and delete data  A DML statement is executed when you: Add new rows to a table Modify existing rows in a table Remove existing rows from a table A transaction consists of a collection of DML statements that form a logical unit of work. Database Systems 1  New DEPARTMENTS row Insert new row into the DEPARTMENTS table.  Add new rows to a table by using the INSERT statement: INSERT INTO table [(column [, column...])] VALUES (value [, value...]); With this syntax, only one row is inserted at a time. Adds one or more rows to a table Inserting into a table Inserting a record that has some null attributes requires identifying the fields that actually get data Inserting from another table  Introduced with SQL:2008 Inserting into a table does not require explicit customer ID entry or field list INSERT INTO CUSTOMER_T VALUES ( ‘Contemporary Casuals’, ‘1355 S. Himes Blvd.’, ‘Gainesville’, ‘FL’, 32601);  Use & substitution in a SQL statement to prompt for values. & is a placeholder for the variable value. INSERT INTO departments (department_id, department_name, location_id) VALUES (&department_id, '&department_name',&location); EMPLOYEES Update rows in the EMPLOYEES table:  Modify existing values in a table with the UPDATE statement: UPDATE table SET column = value [, column = value,...] [WHERE condition]; Update more than one row at a time (if required). Modifies data in existing rows DEPARTMENTS Delete a row from the DEPARTMENTS table: You can remove existing rows from a table by using the DELETE statement: DELETE [FROM] table [WHERE condition]; Removes rows from a table Delete certain rows DELETE FROM CUSTOMER_T WHERE CUSTOMERSTATE = ‘HI’; Delete all rows DELETE FROM CUSTOMER_T; Makes it easier to update a table…allows combination of Insert and Update in one statement Useful for updating master tables with new data END OF SUBTOPIC 1  MODULE 7 SUBTOPIC 2 Transaction Control Commands At the end of the chapter, the learner should be able to: Write single table queries using SQL SELECT command Understand the function of transaction control statements A database transaction consists of one of the following: DML statements that constitute one consistent change to the data One DDL statement One data control language (DCL) statement Time COMMIT Transaction DELETE SAVEPOINT A INSERT UPDATE SAVEPOINT B INSERT ROLLBACK ROLLBACK ROLLBACK to SAVEPOINT B to SAVEPOINT A  Create a marker in the current transaction by using the SAVEPOINT statement. Roll back to that marker by using the ROLLBACK TO SAVEPOINT statement. UPDATE... SAVEPOINT update_done; INSERT... ROLLBACK TO update_done;  Make the changes: DELETE FROM employees WHERE employee_id = 99999; INSERT INTO departments VALUES (290, 'Corporate Tax', NULL, 1700); Commit the changes: COMMIT; Discard all pending changes by using the ROLLBACK statement: Data changes are undone. Previous state of the data is restored. Locks on the affected rows are released. DELETE FROM copy_emp; ROLLBACK ; DELETE FROM test; 25,000 rows deleted. ROLLBACK; Rollback complete. DELETE FROM test WHERE id = 100; 1 row deleted. SELECT * FROM test WHERE id = 100; No rows selected. COMMIT; Commit complete.  Read consistency guarantees a consistent view of the data at all times. Changes made by one user do not conflict with the changes made by another user. Read consistency ensures that, on the same data: ✓Readers do not wait for writers ✓Writers do not wait for readers ✓Writers wait for writers User A UPDATE employees Data SET salary = 7000 blocks WHERE last_name = 'Grant'; Undo segments Changed SELECT * and unchanged FROM userA.employees; Read- data consistent image Before change User B (“old” data)  Locks the rows in the EMPLOYEES table where job_id is SA_REP. SELECT employee_id, salary, commission_pct, job_id FROM employees WHERE job_id = 'SA_REP' FOR UPDATE ORDER BY employee_id; Lock is released only when you issue a ROLLBACK or a COMMIT. If the SELECT statement attempts to lock a row that is locked by another user, then the database waits until the row is available, and then returns the results of the SELECT statement.  You can use the FOR UPDATE clause in a SELECT statement against multiple tables. SELECT e.employee_id, e.salary, e.commission_pct FROM employees e JOIN departments d USING (department_id) WHERE job_id = 'ST_CLERK‘ AND location_id = 1500 FOR UPDATE ORDER BY e.employee_id; Rows from both the EMPLOYEES and DEPARTMENTS tables are locked. Use FOR UPDATE OF column_name to qualify the column you intend to change, then only the rows from that specific table are locked. Projection Selection Table 1 Table 1 Join Table 1 Table 2 Used for queries on single or multiple tables Clauses of the SELECT statement: SELECT List the columns (and expressions) to be returned from the query FROM Indicate the table(s) or view(s) from which data will be obtained WHERE Indicate the conditions under which a row will be included in the result GROUP BY Indicate categorization of results HAVING Indicate the conditions under which a category (group) will be included ORDER BY Sorts the result according to specified criteria SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; Find products with standard price less than $275 Table 6-3: Comparison Operators in SQL Alias is an alternative column or table name SELECT CUST.CUSTOMERNAME AS NAME, CUST.CUSTOMERADDRESS FROM CUSTOMER_V CUST WHERE NAME = ‘Home Furnishings’;  Using the COUNT aggregate function to find totals SELECT COUNT(*) FROM ORDERLINE_T WHERE ORDERID = 1004; Note: With aggregate functions you can’t have single-valued columns included in the SELECT clause, unless they are included in the GROUP BY clause. AND, OR, and NOT Operators for customizing conditions in WHERE clause Note: The LIKE operator allows you to compare strings using wildcards. For example, the % wildcard in ‘%Desk’ indicates that all strings that have any number of characters preceding the word “Desk” will be allowed. Figure 6-8 Boolean query A without use of parentheses By default, processing order of Boolean operators is NOT, then AND, then OR With parentheses…these override the normal precedence of Boolean operators With parentheses, you can override normal precedence rules. In this case parentheses make the OR take place before the AND. Figure 6-9 Boolean query B with use of parentheses Sort the results first by STATE, and within a state by the CUSTOMER NAME Note: The IN operator in this example allows you to include rows whose CustomerState value is either FL, TX, CA, or HI. It is more efficient than separate OR conditions. Database Systems 1 For use with aggregate functions Scalar aggregate: single value returned from SQL query with aggregate function Vector aggregate: multiple values returned from SQL query with aggregate function (via GROUP BY) You can use single-value fields with aggregate functions if they are included in the GROUP BY clause Database Systems 1 For use with GROUP BY Like a WHERE clause, but it operates on groups (categories), not on individual rows. Here, only those groups with total numbers greater than 1 will be included in final result. Database Systems 1 EMPLOYEES table Feature Simple Views Complex Views Number of tables One One or more Contain functions No Yes Contain groups of No Yes data DML operations Yes Not always through a view Views provide users controlled access to tables Base Table–table containing the raw data Dynamic View A “virtual table” created dynamically upon request by a user No data actually stored; instead data from base table made available to user Based on SQL SELECT statement on base tables or other views Materialized View Copy or replication of data Data actually stored Must be refreshed periodically to match corresponding base tables Database Systems 1  You embed a subquery in the CREATE VIEW statement: CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; The subquery can contain complex SELECT syntax. Database Systems 1  View has a name. View is based on a SELECT statement. CHECK_OPTION works only for updateable views and prevents updates that would create rows not included in the view.  Simplify query commands Assist with data security (but don't rely on views for security, there are more important security measures) Enhance programming productivity Contain most current base table data Use little storage space Provide customized view for user Establish physical data independence Database Systems 1  Use processing time each time view is referenced May or may not be directly updateable Database Systems 1 In this lesson, you should have learned how to use the following statements: Function Description INSERT Adds a new row to the table UPDATE Modifies existing rows in the table DELETE Removes existing rows from the table MERGE Combines of Insert and Update in one statement COMMIT Makes all pending changes permanent SAVEPOINT Is used to roll back to the savepoint marker ROLLBACK Discards all pending data changes FOR UPDATE clause Locks rows identified by the SELECT query in SELECT END OF MODULE 7  Taylor, A. G. (2019). SQL for dummies (9th ed.). Hoboken, NJ: For Dummies. Harrington, J. (2016). Relational Database Design and Implementation (4th Edition). Morgan Kaufmann Juric, N., Vrbsky, S., Nestorov, S. (2016). Database Systems: Introduction to Databases and Data Warehouses. Prospect Press Kroenke, D. M., & Auer, D. J. (2016). Database Concepts. Pearson. Sullivan, D. (2015). NoSQL for Mere Mortals (1st ed.). Boston: Addison- Wesley. Hoffer, J., Ramesh, V., Topi, H. (2013). Modern Database Management 11th Edition, Prentice Hall. Introduction to Programming INFORMATION MANAGEMENT MODULE 8: Advanced SQL and Data Control Language (DCL)  MODULE 8 SUBTOPIC 1 ADVANCED SQL  MODULE 8 OBJECTIVES At the end of the chapter, the learner should be able to: Define terms Write single and multiple table SQL queries Define and use three types of joins Write noncorrelated and correlated subqueries Differentiate system privileges from object privileges Grant privileges on tables Grant roles Distinguish between privileges and roles EMPLOYEES DEPARTMENTS … … Use a join to query data from more than one table: SELECT table1.column, table2.column FROM table1 [NATURAL JOIN table2] | [JOIN table2 USING (column_name)] | [JOIN table2 ON (table1.column_name = table2.column_name)]| [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]| [CROSS JOIN table2]; Database Systems 1 Join–a relational operation that causes two or more tables with a common domain to be combined into a single table or view Equi-join–a join in which the joining condition is based on equality between values in the common columns; common columns appear redundantly in the result table Natural join–an equi-join in which one of the duplicate columns is eliminated in the result table The common columns in joined tables are usually the primary key of the dominant table and the foreign key of the dependent table in 1:M relationships. Database Systems 1  Outer join–a join in which rows that do not have matching values in common columns are nonetheless included in the result table (as opposed to inner join, in which rows must have matching values in order to appear in the result table) Union join–includes all columns from each table in the join, and an instance for each row of each table Database Systems 1 Figure 7-2 Visualization of different join types with results returned in shaded area The following slides create tables for this enterprise data model (from Chapter 1, Figure 1-3) Figure 7-1 Pine Valley Furniture Company Customer_T and Order_T tables with pointers from customers to their orders These tables are used in queries that follow For each customer who placed an order, what is the customer’s name and order number? Customer ID appears twice in the result Database Systems 1 INNER JOIN clause is an alternative to WHERE clause, and is used to match primary and foreign keys. An INNER join will only return rows from each table that have matching rows in the other. This query produces same results as previous equi-join example. For each customer who placed an order, what is the customer’s name and order number? Join involves multiple tables in FROM clause Note: From Fig. 7-1, you see that ON clause performs the only 10 Customers have links with equality check for common orders. columns of the two tables ➔ Only 10 rows will be returned from this INNER join Database Systems 1 List the customer name, ID number, and order number for all customers. Include customer information even for customers that do have an order. LEFT OUTER JOIN clause Unlike INNER join, this causes customer data to will include customer appear even if there is rows with no matching no corresponding order order rows data Database Systems 1 Outer Join Results Unlike INNER join, this will include customer rows with no matching order rows Assemble all information necessary to create an invoice for order number 1006 Four tables involved in this join Each pair of tables requires an equality-check condition in the WHERE clause, matching primary keys against foreign keys. Database Systems 1 Figure 7-4 Results from a four-table join (edited for readability) From CUSTOMER_T table From ORDER_T table From PRODUCT_T table  The same table is used on both sides of the join; distinguished using table aliases Self-joins are usually used on tables with unary relationships. Figure 7-5 Example of a self-join Subquery–placing an inner query (SELECT statement) inside an outer query Options: In a condition of the WHERE clause As a “table” of the FROM clause Within the HAVING clause Subqueries can be: Noncorrelated–executed once for the entire outer query Correlated–executed once for each row returned by the outer query Database Systems 1 Show all customers who have placed an order Show all customers who have placed an order The IN operator will test to see if the CUSTOMER_ID value of a row is included in the list returned from the subquery Subquery is embedded in parentheses. In this case it returns a list that will be used in the WHERE clause of the outer query Database Systems 1 Some queries could be accomplished by either a join or a subquery Join version Subquery version Database Systems 1 Figure 7-6 Graphical depiction of two ways to answer a query with different types of joins Figure 7-6 Graphical depiction of two ways to answer a query with different types of joins Noncorrelated subqueries: Do not depend on data from the outer query Execute once for the entire outer query Correlated subqueries: Make use of data from the outer query Execute once for each row of the outer query Can use the EXISTS operator Database Systems 1  Figure 7-8a Processing a noncorrelated subquery 26 A noncorrelated subquery processes completely before the outer query begins. Show all orders that include furniture finished in natural ash. The EXISTS operator will return a TRUE value if the subquery resulted in a non-empty set, otherwise it returns a FALSE ➔ A correlated subquery always refers to an attribute from a table referenced in the outer query Database Systems 1  Figure 7-8b Processing a correlated Subquery refers to outer- subquery query data, so executes once for each row of outer query Note: Only the orders that involve products with Natural Ash will be included in the final results. 28 Show all products whose standard price is higher than the average price One column of the subquery is an Subquery forms the derived aggregate function that has an table used in the FROM clause alias name. That alias can then be of the outer query referred to in the outer query. The WHERE clause normally cannot include aggregate functions, but because the aggregate is performed in the subquery its result can be used in the outer query’s WHERE clause. Database Systems 1 Combine the output (union of multiple queries) together into a single result table First query Combine Second query Database Systems 1 This is available with newer versions of SQL, previously not part of the standard Figure 7-10  Be familiar with the data model (entities and relationships) Understand the desired results Know the attributes desired in results Identify the entities that contain desired attributes Review ERD Construct a WHERE equality for each link Fine tune with GROUP BY and HAVING clauses if needed Consider the effect on unusual data Database Systems 1  Instead of SELECT *, identify the specific attributes in the SELECT clause; this helps reduce network traffic of result set Limit the number of subqueries; try to make everything done in a single query if possible If data is to be used many times, make a separate query and store it as a view Database Systems 1  Understand how indexes are used in query processing Keep optimizer statistics up-to-date Use compatible data types for fields and literals Write simple queries Break complex queries into multiple simple parts Don’t nest one query inside another query Don’t combine a query with itself (if possible avoid self-joins) Database Systems 1  Create temporary tables for groups of queries Combine update operations Retrieve only the data you need Don’t have the DBMS sort without an index Learn! Consider the total query processing time for ad hoc queries Database Systems 1 END OF SUBTOPIC 1  MODULE 8 SUBTOPIC 2 DATA CONTROL LANGUAGE (DCL)  MODULE 8 OBJECTIVES At the end of the chapter, the learner should be able to: Differentiate system privileges from object privileges Grant privileges on tables Grant roles Distinguish between privileges and roles  Database administrator Username and password Privileges Users  Database security: - System security - Data security System privileges: Performing a particular action within the database Object privileges: Manipulating the content of the database objects Schemas: Collection of objects such as tables, views, and sequences Database Systems 1  More than 100 privileges are available. The database administrator has high-level system privileges for tasks such as: Creating new users Removing users Removing tables Backing up tables Database Systems 1  The database administrator (DBA) creates users with the CREATE USER statement. CREATE USER user IDENTIFIED BY password; CREATE USER demo IDENTIFIED BY demo; Database Systems 1  After a user is created, the DBA can grant specific system privileges to that user. GRANT privilege [, privilege...] TO user [, user| role, PUBLIC...]; An application developer, for example, may have the following system privileges: CREATE SESSION CREATE TABLE CREATE SEQUENCE CREATE VIEW CREATE PROCEDURE Database Systems 1 The DBA can grant specific system privileges to a user. GRANT create session, create table, create sequence, create view TO demo; Database Systems 1 Give a user authority to pass along system privileges: GRANT create session, create table TO demo WITH ADMIN OPTION; Database Systems 1  Users Manager Privileges Allocating privileges Allocating privileges without a role with a role Database Systems 1  Create a role: CREATE ROLE manager; Grant privileges to a role: GRANT create table, create view TO manager; Grant a role to users: GRANT manager TO BELL, KOCHHAR; Database Systems 1  The DBA creates your user account and initializes your password. You can change your password by using the ALTER USER statement. ALTER USER demo IDENTIFIED BY employ; Database Systems 1 Object privilege Table View Sequence ALTER DELETE INDEX INSERT REFERENCES SELECT UPDATE  Object privileges vary from object to object. An owner has all the privileges on the object. An owner can give specific privileges on that owner’s object. GRANT object_priv [(columns)] ON object TO {user|role|PUBLIC} [WITH GRANT OPTION]; Database Systems 1  Grant query privileges on the EMPLOYEES table: GRANT select ON employees TO demo; Grant privileges to update specific columns to users and roles: GRANT update (department_name, location_id) ON departments TO demo, manager; Database Systems 1  Give a user authority to pass along privileges: GRANT select, insert ON departments TO demo WITH GRANT OPTION; Allow all users on the system to query data from Alice’s DEPARTMENTS table: GRANT select ON alice.departments TO PUBLIC; Database Systems 1 Data Dictionary View Description ROLE_SYS_PRIVS System privileges granted to roles ROLE_TAB_PRIVS Table privileges granted to roles USER_ROLE_PRIVS Roles accessible by the user USER_SYS_PRIVS System privileges granted to the user USER_TAB_PRIVS_MADE Object privileges granted on the user’s objects USER_TAB_PRIVS_RECD Object privileges granted to the user USER_COL_PRIVS_MADE Object privileges granted on the columns of the user’s objects USER_COL_PRIVS_RECD Object privileges granted to the user on specific columns  You use the REVOKE statement to revoke privileges granted to other users. Privileges granted to others through the WITH GRANT OPTION clause are also revoked. REVOKE {privilege [, privilege...]|ALL} ON object FROM {user[, user...]|role|PUBLIC} [CASCADE CONSTRAINTS]; Database Systems 1 Revoke the SELECT and INSERT privileges given to the demo user on the DEPARTMENTS table. REVOKE select, insert ON departments FROM demo; Database Systems 1 In this lesson, you should have learned about statements that control access to the database and database objects. Statement Action CREATE USER Creates a user (usually performed by a DBA) GRANT Gives other users privileges to access the objects CREATE ROLE Creates a collection of privileges (usually performed by a DBA) ALTER USER Changes a user’s password REVOKE Removes privileges on an object from users END OF MODULE 8  Taylor, A. G. (2019). SQL for dummies (9th ed.). Hoboken, NJ: For Dummies. Harrington, J. (2016). Relational Database Design and Implementation (4th Edition). Morgan Kaufmann Juric, N., Vrbsky, S., Nestorov, S. (2016). Database Systems: Introduction to Databases and Data Warehouses. Prospect Press Kroenke, D. M., & Auer, D. J. (2016). Database Concepts. Pearson. Sullivan, D. (2015). NoSQL for Mere Mortals (1st ed.). Boston: Addison- Wesley. Hoffer, J., Ramesh, V., Topi, H. (2013). Modern Database Management 11th Edition, Prentice Hall. Introduction to Programming INFORMATION MANAGEMENT MODULE 9  MODULE 9 DATA AND DATABASE ADMINISTRATION  OBJECTIVES At the end of the chapter, the learner should be able to: List functions and roles of data/database administration Describe problems and techniques for data security Describe problems and facilities for data recovery Describe database tuning issues and list areas where changes can be done to tune the database Describe importance and measures of data availability Data Administration (managerial): responsible for the overall management of data resources in an organization, including data definitions and standards Database Administration (technical): responsible for physical database design and for dealing with technical issues such as security enforcement, database performance, and backup and recovery Same person may perform both roles Database Systems 1  Analyzing and designing databases Selecting DBMS and software tools Installing/upgrading DBMS Troubleshooting Tuning database performance Improving query processing performance Managing data security, privacy, and integrity Data backup and recovery Database Systems 1  Data policies, procedures, standards Planning Data conflict (ownership) resolution Managing the information repository Internal marketing of DA concepts Raise awareness of the importance of practices Database Systems 1  Increased use of procedural logic Proliferation of Internet-based applications Increase use of smart devices Cloud computing and database/data administration Database Systems 1  Multiple dissimilar data definitions, causing data integration problems / poor data value Inappropriate data sources and timing, causing lowered reliability Inadequate familiarity, causing ineffective use of data for planning and strategy Accidental deletion or destruction (update cust set name = “” without using a ‘where?