UTA INSY 3304 - Exam 2 - Chapter 7&9 Latest 2023 Graded A

UTA INSY 3304 - Exam 2 - Chapter 7&9 Latest 2023 Graded A Data definition language DDL Data manipulation language DML CREAT SCHEMA AUTHORIZATION Creates a database schema CREATE TABLE Creates a new table in the user's databse schema NOT NULL Ensures that a column will not have null values UNIQUE Ensures that a column will not have duplicate values PRIMARY KEY Defines a primary key for a table FOREIGN KEY Defines a foreign key for a table DEFAULT Defines a default value for a column ( when no value is give) CHECK validates data in an attribute CREATE INDEX Creates an index for a table CREATE VIEW Creates a dynamic subset of rows and column from on or more tables ALTER TABLE Modifies a table's definition (adds, modifies, or deletes attributes or constraints) CREATE TABLE AS Creates a new table based on a query in the user's database schema DROP INDEX Permanently deletes an index DROP VIEW Permanently deletes a view Authentications the process DBMS uses to verify that only registered users access the database Numeric Number( L, D) or Numeric(L, D) Character Char (L) Date (DATE) Stores dates in the Julian format iNSERT: Command to insert data into table INSERT INTO tablename VALUES(); COMMIT Syntax COMMIT [WORK]; SELECT Syntax Command to list the content SELECT columnlist FROM tablename; UPDATE Command to modify data Syntax - UPDATE tablename SET columnname = expression [, columnname = expression] [WHERE conditionlist]; ROLLBACK Syntax -ROLLBACK; Undoes the changes since last COMMIT command DELETE Command to delete Syntax - DELETE FROM tablename Subquery Query embedded/nested inside another query = Equal to < Less than <= less than or equal to > greater than >= Greater than or equal to <> or != Not equal to Alias Alternate name given to a column or table in any SQL statement to improve the readability The Rule of Precedence Establish the order in which computations are completed Performed in this order: 1. Operations within parentheses 2. Power operations 3.Multiplications and divisions 4.Additions and subtractions OR and AND Used to link multiple conditional expressions in a WHERE or HAVING clause OR requires only one of the conditional expressions to be true AND requires all of the conditional expressions to be true NOT is used to negate the result of a conditional expression Boolean algebra is dedicated to the use to logical operations Between Checks whether attribute value is within a range IS NULL Checks whether attribute value is null LIKE Checks whether attribute value matches given string pattern IN Checks whether attribute value matches any value within a value list EXISTS Checks if subquery returns any rows ALTER TABLE command To make changes in the table structure Use _______________ command to ADD primary and foreign keys ALTER TABLE DROP TABLE Deletes table from database Syntax - DROP TABLE tablename; ORDER BY clause is useful when listing order is important Syntax - SELECT columnlist FROM tablelist [WHERE conditionlist] [ORDER BY columnlist [ASC | DESC]]; Cascading order sequence Multilevel ordered sequence DISTINCT clause Produces list of values that are unique Syntax- SELECT DISTINCT columnlistFROM tablelist; Recursive query Table is joined to itself using alias Systems analysis Process that establishes need for and extent of information system Systems development Process of creating information system Database development Process of database design and its implementation Purpose of Database Initial Study Analyze company situation Define problems and constraints Define objectives Define scope and boundaries Database Design Supports company's operations and objectives Most critical phase Ensures final product meets user and system requirements Virtualization Creates logical representations of computing resources independent of underlying physical computing resources Create the databases Requires the creation of special storage-related constructs to house the end-user tables Load or convert the data Requires aggregating data from multiple sources Full backup/dump All database objects are backed up in their entirety Differential backup Only modified/updated objects since last full backup are backed up Transaction log backup Only the transaction log operations that are not reflected in a previous backup are backed up Software Software-induced failures may be traceable to the operating system, the DBMS software, application program, or viruses and other malware Hardware Hardware-induced failures may include memory chip errors, disk crashes, bad disk sectors, and disk-full errors. Programming exemptions Application program or end users may roll back transactions when certain conditions are defined. Programming exemptions can also be caused by malicious or improperly tested code that can be exploited by hackers. Transactions The system detects deadlocks and aborts one of the transactions External factors Backups are especially important when a system suffers completely destruction from fire, earthquake, flood, or other natural disaster Minimum data rule All that is needed is there, and all that is there is needed Conceptual Design Designs a database independent of database software and physical details Conceptual data model Describes main data entities, attributes, relationships, and constrains Conceptual Design Steps 1. Data analysis and requirements 2. Entity relationship modeling and normalization 3. Data model verification 4. Distributed database design Data Analysis and Requirements "Discover data element characteristics Obtains characteristics from different sources Requires thorough understanding of the company's data types and their extent and uses Take into account business rules Derived from description of operations " Description of Operations Provides precise, up-to-date, and reviewed description of activities defining an organization's operating environment Developing the conceptual model using ER diagrams 1. Identify, analyze, and refine the business rules. 2. Identify the main entities, using the results of Step 1. 3. Define the relationships among the entities, using the result of Step 1 and 2. 4. Define the attributes, primary keys, and foreign keys for each of the entities. 5. Normalize the entities. 6. Complete the initial ER diagram. 7. Validate the ER model against the end users' information and processing requirements. 8. Modify the ER model, using the results of Step 7. Module Information system component that handles specific business function Data Model Verification Verified against proposed system processes Revision of original design Careful reevaluation of entities Detailed examination of attributes describing entities The ER Model Verification process 1. Identify the ER model's central entity 2. Identify each module and its components. 3. Identify each modules transaction requirements 4. Verify all processes against system requirements. 5. Make all necessary changes suggested in Step 4. 6. Repeat steps 2-5 for all modules Cohesivity Strength of the relationships among the module's entities Module coupling Extent to which modules are independent to one another Database fragment Subset of a database stored at a given location Distributed Database Design Portions of database may reside in different physical locations, Ensures database integrity, security, and performance DBMS Software Selection Cost DBMS features and tools Underlying model Portability DBMS hardware requirements Logical design Designs an enterprise-wide database that is based on a specific data model but independent of physical-level details Physical design Process of data storage organization and data access characteristics of the database Logical Design Steps 1. Map the conceptual model to logical model components. 2. Validate the logical model using normalization. 3. Validate the logical model integrity constraints. 4. Validate the logical model against user requirements. Mapping the conceptual model to the relational model 1. Map strong entities. 2. Map supertype/subtype relationships 3. Map weak entities. 4. Map binary relationships. 5. Map higher-degree relationships. Physical Design Steps 1. Define data storage organization 2. Define integrity and security measures 3. Determine performance measurements Clustered Tables Technique that stores related rows from two related tables in adjacent data blocks on disk Database Role Set of database privileges that could be assigned as a unit to a user or group