ISDS 402 Summary Notes PDF

Preview

Summary

Summary of all you need to know and general concepts ...

Description

Chapter 1 objectives, key terms, and review ● Purpose of a database ● Examples of databases ○ Single-user databases ○ Multiuser databases ○ E-commerce database apps (amazon,online shopping) ○ Reporting and data mining database apps ● What do databases store ● Difference between data and information ○ Data- recorded facts and numbers ○ Database- structure (collection of related tables and other structures) used to hold and store data ○ Information- processed data; set of conclusions from analysis of data ● Most common method to keep track of things Objectives ● Understand the importance of databases in internet web applications and mobile apps ○ In today’s web application(internet) and smartphone environment depends on databases. All today’s internet applications rely on databases from amazon to twitter and google. ○ The importance of database processing increases everyday because databases are used in information systems everywhere - and increasingly so. ● Understand the nature and characteristics of databases ○ The purpose of databases is to keep track of things, lists can be used for this. ○ Relational databases store data in the form of tables , and those tables are designed so that each table stores data about a single theme. ● Understand the potential problems with lists ○ If a list involves more than one theme, modification problems will occur when data are inserted , updated, or deleted ○ Lists with multiple themes need to be broken up and stored in multiple tables, for each theme. ○ Then a column needs to be added to link the tables to each other so that the relationship from a row in one table to a row in another table can be shown ● Understand the reasons for using a database

○ To help people keep track of things and eliminate data inconsistencies and other problems. ○ Non-Relational databases: serves different needs for creating and storing massive amounts of data ○ Relational databases: a database that consists of relations with duplicate rows. Most DBMS products remove duplicate rows when necessary and appropriate ● Understand how using related tables helps you avoid problems of using lists ○ How it solves the modifications problems of lists: ■ Insert new data to a table independently without creating null values ■ Update data without creating inconsistencies or wasting time ■ Delete data without unintended consequences ■ You can design a database so that deletion of a row is not allowed if other rows depend on it, or the dependent rows are deleted as well ○ Remove problems and ambiguities by creating a separate table for each theme ● Know the 4 components of a database system ○ Users, the database application, the database management system (DBMS), and the database

● Learn the elements of a database ○ Database: collection of related tables and other structures ● Learn the purpose of a database management system DBMS ○ DBMS: a large complicated computer program licensed from a software vendor used to create, process, and administer the database. Receives requests encoded in SQL and translates into actions on the database ● Understand the functions of a database application

○ Set of one or more computer programs between user and DBMS. Application programs read or modify database data by sending SQL statements to the DBMS, present data to users in the format of forms and reports, and can be acquired from software vendors but frequently written in-house ● Introduce web database applications ○ When an application with a web user interface, is dependent on a database to store the data needed by the application. Ex) amazon.com ● Introduce data warehouses and business intelligence BI systems ○ Data warehouses: a place to store the data for data analysis in a business intelligence (BI) system ○ BI: tools used to analyze and report on company data ● Introduce big data and cloud computing ○ Big data: enormous datasets generated by web and mobile apps such as search tools, social networks, and scientific data collection tools. Ex) google and bing, facebook and twitter ○ Cloud computing: term used to denote apps run on hardware and network resources located at a hosted data center instead of owned and operated by the company. Thus the servers are said to be “in the cloud”. Ex) Office 365, Microsoft Azure Chapter 2 objectives and review ● Learn the conceptual foundation of the relational model ○ DBMS store data in the form of relations, which is a two-dimensional table consisting of rows and columns with the following characteristics: ● Understand how relations differ from non relational tables

○ Non-relational tables: i. column(s) with multiple entries ii. The order of rows can be important

iii. Not all values in a column are the same kind iv. Duplicate rows ○ Violates the characteristics of relational models ● Learn basic relational terminology

● Learn the meaning and importance of keys, foreign keys, and related terminology ○ Key: one or more columns of a relations that can identify a row; may or may not be unique; used to identify a row ○ Composite key: a key that contains two or more attributes; combination of two or more keys ○ Candidate key: keys that uniquely identify each row; can be single column keys or composite keys “in the running to become a primary key” - the losers become alternate keys ○ Primary key: the candidate key in a DBMS chosen to uniquely identify each row; used to link tables; must have unique data values inserted into every row ● Understand how foreign keys represent relationships ○ Foreign key: attribute(s) that is a key(s) of one or more relations other than the one in which it appears ○ Primary key of a relation that is foreign to the table in which it resides:

 DepartmentName in the Department i n EMPLOYEE is the foreign key; Department = DEPARTMENT relation ● Learn the purpose and use of surrogate keys ○ Surrogate key: a column with a unique DBMS-assigned identifier that is added to a table to be the primary key; short and numeric and never changes-ideal primary key; often hidden

○ Used to ensure the primary key is short and numeric ● Learn the meaning of functional dependencies ○ Functional dependency: a relationship between attributes in which one attribute or group of attributes is dependent on the value of another Ex) in math the value of Y is dependent on X ● Learn to apply a process for normalizing relations ○ Normalization: the process of determining whether a relation is in a specified normal form, and if not converting it to relations in that specified normal form ○ Normalization process: i. Identify all the candidate keys of the relation. ii. Identify all the functional dependencies in the relation. iii. Examine the determinants of the functional dependencies. If any determinant is not a candidate key, the relation is not well formed. In this case: 1. Place the columns of the functional dependency in a new relation of their own. 2. Make the determinant of the functional dependency the primary key of the new relation. 3. Leave a copy of the determinant as a foreign key in the original relation. 4. Create a referential integrity constraint between the original relation and the new relation. iv. Repeat step 3 as many times as necessary until every determinant of every relation is a candidate key

Chapter 3 Objectives and review CREATE DATABASE JohnsServices;  CREATE TABLE S  HOPPING( ItenName Char(30) ItemAmount Integer  ); CREATE TABLE C  HORES( ChoreName Char(30)

NOT NULL NULL

 );  CREATE TABLE Y  ARDWORK();   ETCARE(); CREATE TABLE P ● Learn Basic SQL statements for creating database structures  ○ Syntax: CREATE D  ATABASE databasename; ■ database_name: name of the database. ○ Data definition Language (DDL) statements, which are used for creating tables, relationships, and other structures ○ Data manipulation language(DML) statements used for querying, inserting, modifying, and deleting data. One component of SQL DML is SQL views, which are used to create predefined queries ○ SQL/Persistent stored modules (SQL/PSM) statements which extend SQL by adding procedural programming capabilities, such as variables and flow-of-control statements, that provide some programmability within the SQL framework ○ Transaction Control language(TCL) statements which are used to mark transaction boundaries and control transaction behavior ○ Data control language (DCL) statements used to grant database permissions (or revoke) to users and groups s that those users or groups can perform various operations on the data in the database ● Learn Basic SQL statements for adding data to a database ○ Two ways to write INSERT INTO statements ○ #1INSERT INTO table_name (column1, column2, column3, ...) ○ VALUES (value1, value2, value3, ...); ○ Ex)INSERT INTO Customer (FirstName, LastName, City, Country, Phone) ○ VALUES ('Craig', 'Smith', 'New York', 'USA', 1-01-993 2800) ○ #2 If your adding values to all the columns you don’t need to specify each attribute name, just make sure all the values are in same order as the  ALUES (value1 ,   value2,  columns: INSERT INTO table_name V  ...); value3, ● Learn Basic SQL SELECT statements and options for processing a single table

○ Basic form of SQL statement framework: ■ SELECT specifies which columns/attributes are to be listed in the query results ■ FROM specifies which tables are to be used in the query ■ WHERE specifies which rows are to be listed in the query; also used in UPDATE and DELETE statements   column2, ... SELECT column1, FROM table_name  WHERE condition; Ex SELECT * FROM Customers WHERE Country='Mexico'; ○ Ex:SQL statement to obtain values in a table   column2, ... SELECT column1, FROM table_name;  Textbook ex) /* *** SQL-Query-CH03-01 *** */ SELECT ProjectID, ProjectName, Department, MaxHours, StartDate, EndDate FROM PROJECT; ○ Ex: shorthand for querying all columns of a table (use an *) SELECT * FROM table_name;  Textbook ex) /* *** SQL-Query-CH03-02 *** */ SELECT * FROM PROJECT; ○ Ex: have the DBMS eliminate duplicate rows use DISTINCT SELECT DISTINCT column1,   column2, ...  FROM table_name; Textbook ex) /* *** SQL-Query-CH03-06 *** */ SELECT DISTINCT Department FROM PROJECT; ○ Ex: ● Learn Basic SQL SELECT statements for processing multiple tables with subqueries

○ Querying multiple tables with subqueries: subqueries are effective as long as the results are coming from a single table ■ A query of employee number based on hours worked /* *** SQL-Query-CH03-40 *** */ SELECT DISTINCT EmployeeNumber FROM ASSIGNMENT WHERE HoursWorked > 50; ■ Query of employee names based on employee number subquery /* *** SQL-Query-CH03-41 *** */ SELECT FirstName, LastName FROM EMPLOYEE WHERE EmployeeNumber IN (SELECT DISTINCT EmployeeNumber FROM ASSIGNMENT WHERE HoursWorked > 50); ■ Employee names using multiple subqueries /* *** SQL-Query-CH03-44 *** */ SELECT FirstName, LastName FROM EMPLOYEE WHERE EmployeeNumber IN (SELECT DISTINCT EmployeeNumber FROM ASSIGNMENT WHERE HoursWorked > 40 AND ProjectID IN (SELECT ProjectID FROM PROJECT WHERE Department = 'Accounting')); ● Learn Basic SQL SELECT statements for processing multiple tables with joints ○ Querying multiple tables with JOIN: if we need to display data from multiple tables we need to use the join operator, which creates another relation by sticking rows of one table to another ○ You can do this by listing the table names (CROSS JOIN) NEVER DO THIS

○ Inner Join:  selects records that have matching values in both tables; add a WHERE clause that requires that the values in the two columns to equal each other /* *** SQL-Query-CH03-46 *** * a join using the where clause/ SELECT FirstName, LastName, ProjectID, HoursWorked FROM EMPLOYEE, ASSIGNMENT WHERE EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNumber; ○ Join On /* *** SQL-Query-CH03-46 *** * clause/ SELECT FirstName, LastName, ProjectID, HoursWorked FROM EMPLOYEE JOIN ASSIGNMENT ON EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNumber; ○ Group By ○ Order By ● Learn Basic SQL statements for modifying and deleting data from a database  HERE condition;  ○ Delete: D  ELETE F  ROM table_name W ○ The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted! The textbook format: DELETE FROM PROJECT WHERE Department = 'Sales and Marketing'; ○ Modify *Do Not Forget the WHERE clause*: UPDATE table_name SET column1 =   value1,   column2 =   value2,  ... WHERE condition;  ○ Modifying a Null value to have a value (textbook ex) /* *** SQL-Query-CH03-57 *** */ SELECT * FROM EMPLOYEE WHERE OfficePhone IS NULL;

○ Modify to: /* *** SQL-UPDATE-CH03-01 *** */ UPDATE EMPLOYEE SET OfficePhone = '360-287-8620' WHERE EmployeeNumber = 13; ● Learn Basic SQL modifying and deleting database tables and constraints ○ Delete database relation: SQL DROP TABLE statement drops table structure and data; be careful because it deletes complete info stored in a table   ABLE table_name; ○ DROP T ○ Ex: DROP TABLE ASSIGNMENT; ○ ○ ○ ○ ○ ○ ○ ○ ○ ○ ○ ○

Modifying: TRUNCATE TABLE is used to delete only the data in a table  TRUNCATE T  ABLE table_name; Ex: TRUNCATE TABLE PROJECT; ALTER TABLE statement is to add, modify, and drop attributes Add column: ALTER TABLE table_name ADD column_name datatype;   ABLE Customers Ex: A  LTER T ADD Email varchar(255); Drop column: A  LTER TABLE table_name DROP C  OLUMN column_name;  Ex: A  LTER T  ABLE Customers DROP COLUMN Email; ALTER TABLE ASSIGNMENT DROP CONSTRAINT ASSIGN_EMP_FK; Modify data type in a column: A  LTER T  ABLE table_name ALTER C  OLUMN column_name datatype;  Ex: A  LTER T  ABLE Persons ALTER C  OLUMN DateOfBirth year;

Chapter 4 Objectives and review ● Learn the basic stages of database development ○ Database development process: a subset of the systems development life cycle SDLC that specifically designs and implements the database. ○ The 5 stage cycle in the database development process

■ System definition: step is a process that starts with the need for an information system to support a business process as its input and produces a project plan as its output. During this process, we will need to: ● Define the information system project goals and scope. Assess the feasibility of the project (financial [cost], temporal [schedule], technical, organizational). Form the project team. Plan the project (specify tasks, assign personnel, determine task dependencies, set schedules ■ Requirements analysis: system users are interviewed and sample forms, reports, queries, and descriptions of update activities are obtained to create a data model (most focused on in this textbook) ● Data model is a representation of the content, relationships, and constraints on the data needed to support system requirements ■ Component design: the data model is transformed into a database design, consisting of tables, relationships, and constraints; design includes table and column names, data types and properties of the columns as well as a description of primary and foreign keys ● Data constraints consist of limits on data values (ex. Part numbers are 7 digit numbers starting with the number 3), referential integrity constraints, and business rules(ex. Every purchased part will have at least two quotes from supplies). ■ Implementation: the database is constructed in the DBMS and populated with data; queries, forms, and reports are created; application programs are written; everything is tested. During this stage users are trained, documentation is written, and the new system is put into use ■ System maintenance: Update the system with patches, service packs, and new software releases. Record and prioritize requests for system changes or enhancements. ● The deliverables for system maintenance include an updated system and the start of a new SDLC cycle to enhance the information system. These are both common and typical events for any information system ● Understand the purpose and role of a data model ○ Data model is basically the conceptual design; a language for describing the structure and processing of a database ● Know the principal components of the E-R data model

○ Extended entity-relationship model (E-R model): the constructs to create a model of user’s data. Things in the user’s world are represented by entities and the association among those things are represented by relationships. Results are usually documents in an E-R diagram ■ Elements: ● Entities: something the user wants to track ● Attributes: describe the entities characteristics ● Identifiers(candidate keys): attributes that name, or identify, entity instances; can be non/unique and/or composite ● Relationships: an association between two entities, objects, or rows of relations ● Understand how to interpret traditional E-R diagrams ○ One-to-one (1:1) relationship ○ One-to-many (1:N) relationship ○ Many-to-many (N:M) relationship ○ The numbers inside the relationship diamond show the maximum number of entity instances that can occur on each side of the relationship ○ Hash mark: next to an entity, the other entity must be related to at least one instance ○ Oval: next to an entity indicates the other entity doesn’t need to be related to any instance of said entity. Ex) ITEM must have a relationship with at least one SUPPLIER but SUPPLIER doesn’t need to be related to ITEM

● Understand how to interpret the information engineering (IE) model’s Crow’s Foot E-R diagrams ○ Crow’s foot model(Info engineering IE model): system os symbology used to construct E-R diagrams in data modeling and database design ○ ● Learn to construct E-R diagrams ○ ● Know how to represent 1:1, 1:N, N:M, and binary relationships with the E-R model. E-R Diagrams:

● Understand two types of weak entities and know how to use them ● Understand non-identifying and identifying relationships and know how to use them ● Know how to represent subtype entities with the E-R model ● Know how to represent recursive relationships with the E-R model ● Learn how to create an E-R diagram from source documents ●

●

●

●

●

Entity Class: description of the structure and occurrences ○ Ex. recipe or blueprint ○ table Entity Instance: specific occurence of an entity class ○ Ex. The unique result ○ Dummy data Attribute: specific piece of info. To keep track of ○ Ex. projecName, startDate ○ Each attribute has a data type 2 types of Attribute ○ Identifiers (keys) ■ Values are used to identify instance of entity class ■ Ex. IDs ■ Can be unique or non-unique ○ Composite Key ■ Two or more attributes Relationships: how entities are connected to each other ○ Relationships degrees- the number of entities per relationship ■ Degree 1: Unary - entity related to itself — ■ Degree 2: Binary ● Ex. employee ←→ parking space

● Degree 3: Ternary ● Ex. Prescription → doctor, patient, drug Use a look-up table to connect a many to many relationship ■

●

●

●

○ 2 types of Cardinalities (Counts) ○ Maximum Cardinality ■ 1, many, Positive number ■ Max number of instances in relationships ○ Minimum Cardinality ■ Zero (optional) or 1 (mandatory) ■ Min number of instances that must participate in relationship

■ ■ Max to the right, min to the left Weak entity: instances cannot exist unless they are related to an instance in another entity

● ● ●

Strong entity: can exist in a database independently Subtype entity: special case of another entity Recursive Relationship: entity has a unary relationship to itself ○ Ex. referrals

Chapter 5 Objectives and Review ● Learn how to transform E-R data models into relational designs

● Practice applying the normalization process (pg. 90)

○ ● Understand the need for denormalization ○ Intentionally designing a relation that is not fully normalized for better performance and security of the DBMS and not cause modification problems

● Learn how to represent weak entities with the relational model ○ If a w...