IFB130 Notes PDF

Preview

Summary

Notes needed for the first exam...

Description

IFB130 Notes

Database A shared collection of logically related data, and a description of this data, designed to meet the information needs of an organization. Database Management System (DBMS) A software system that enables users to define, create, maintain, and control access to the database. Relational Database Management System(rDBMS) Term used to refer to a DBMS that uses the relational model. Database Application Program Computer program that interacts with the database by issuing an appropriate request (typically an SQL statement) to the DBMS. Database System Used to define a collection of application programs that interact with the database along with the DBMS and database itself. ANSI-SPARC (3-level) architecture Uses three levels of abstraction: external, conceptual, and internal. External level (ANSI-SPARC architecture) Consists of the users’ views of the database. Conceptual Level (ANSI-SPARC architecture) The community view of the database. It specifies the information content of the entire database, independent of storage con- siderations. The conceptual level represents all entities, their attributes, and their relationships, as well as the constraints on the data, and security and integrity information. Internal Level (ANSI-SPARC architecture) The computer’s view of the database. It specifies how data is represented, how records are sequenced, what indexes and pointers exist, and so on. Database Schema Description of the database structure. Data independence Makes each level of a database schema immune to changes to lower levels.

Logical Data Independence The immunity of the external scemas to changes in the conceptual schema. Physical Data Independence The immunity of the conceptual schema to changes in the internal schema. Data Model Collection of concepts that can be used to describe a set of data, the operations to manipulate the data, and a set of integrity constraints for the data. Conceptual Modeling The process of constructing a detailed architecture for a database that is independent of implementation details, such as the target DBMS, application programs, programming languages, or any other physical considerations. The design of the conceptual schema is critical to the overall success of the system. It is worth spending the time and energy necessary to produce the best possible conceptual design. Data define language (DDL) Specifies data types and structures Data manipulation language (DML) Allows insertion, deletion and retrieval Entity Relationship Diagram (ERD) A conceptual data model for describing the data or information aspects of an organisation, which lends itself to ultimately being implemented in a database (such as a relational database). Unified Modeling Language (UML) A general-purpose, developmental, modeling language in the field of software engineering. Entity Type A group of objects with the same properties, which are identified by the enterprise as having an independent existence. An Entity is a thing in the real world with an independent existence. An entity may be an object with a physical existence (for example, a particular person, car, house, or employee) or it may be an object with a conceptual existence (for instance, a company, a job, or a university course). Entity Occurrence A uniquely identifiable object of an entity type. Strong Entity Type Entity type that is not existence-dependent on some other entity type. Uniquely identifiable using the primary key attribute.

Weak Entity Type Entity type that is existence-dependent on some other entity type. Can only identify attributes of weak entity types through relationships with strong entity types. Attribute A property of an entity or relationship type. Simple Attribute An attribute composed of a single component with an independent existence. Composite Attribute An attribute composed of multiple components, each with an independent existence. Composite attributes can be divided into smaller subparts, which represent more basic attributes with independent meanings. Single-valued Attribute An attribute that holds a single value for each occurrence of an entity type. Multi-valued Attribute An attribute that holds multiple values for each occurrence of an entity type. Domain An attribute domain is the set of allowable values for one or more attributes. Derived Attribute An attribute that represents a value that is derivable from the value of a related attribute or set of attributes, not necessarily in the same entity. Relationship Type a set of meaningful associations among entity types. A relationship occurrence is a uniquely identifiable association, which includes one occurrence from each participating entity type. Recursive Relationship A relationship type where the same entity type participates more than once in different roles.

Multiplicity The number (or range) of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particular relationship (e.g. 1..n to n..n). Cardinality Describes the maximum number of possible relationship occurrences for an entity participating in a given relationship type (e.g. Many to Many). Fan Trap Where a model represents a relationship between entity types, but the pathway between certain entity occurrences is ambiguous. Chasm Trap Where a model suggests the existence of a relationship between entity types, but the pathway does not exist between certain entity occurrences. Specialization is the process of maximizing the differences between members of an entity by identifying their distinguishing characteristics. Specialization refers to when we attempt to identify the distinguishing features between members of an entity type Generalization is the process of minimizing the differences between entities by identifying their common characteristics. Superclass is an entity type that includes one or more distinct subgroupings of its occurrences, which must be represented in a data model. Subclass is a distinct subgrouping of occurrences of an entity type, which must be represented in a data model. An entity in a subclass represents the same “real world” object as in the superclass, and may possess subclass-specific attributes, as well as those associated with the superclass e.g. Manager is a specialisation of Staff and has the additional attributes mngrStartDate and bonus.

Attribute Inheritance An occurrence in a subclass inherits all the attributes of the superclass. Aggregation Represents a “has-a” or “is-part-of” relationship between entity types, where one represents the “whole” and the other the “part.” Composition Is a specific form of aggregation that represents an association between entities, where there is a strong ownership and coincidental lifetime between the “whole” and the “part. Participation Constraint Determines whether every member in the superclass must participate as a member of a subclass. Disjoint Constraint Describes the relationship between members of the subclasses and indicates whether it is possible for a member of a superclass to be a member of one, or more than one, subclass. Degree The degree of a relationship type is the number of participating entity types. Binary Relationship 2 entity types participate in the relationship. Ternary Relationship 3 entity types participate in the relationship. Quaternary Relationship 4 entity types participate in the relationship. Database Design Process Use case -> ERD -> RM -> Normalization -> SQL -> Optimize Concepts of ER model Entity types, Relationship types, Attributes, Multiplicity Relationship occurrence Uniquely identifiable association, with at least one entity type Transaction pathways Support verification of ERD against requirements of DB

Conceptual Data Model 1. Entity Types 2. Relationship types 3. Identify Attributes 4. Identify Attribute Domains 5. Find candidate, primary and alternate keys 6. Multiplicity 7. Validate Conceptual Model Relational Model A logical data model, where all data is represented in terms of tuples, grouped into relations. A database organized in terms of the relational model is a relational database. Relation (Table, File) - A table with columns and rows. It has the following properties - Has a name that is distinct from all other relation names in the relational schema - Each cell of the relation contains exactly one atomic (single) value; - Each attribute has a distinct name; - The values of an attribute are all from the same domain; - Each tuple is distinct; there are no duplicate tuples; - The order of attributes has no significance; and - The order of tuples has no significance, theoretically. Attribute (Column, Field) A named column of a relation, which describes a property of the corresponding entity or a relationship type. Tuple (Row, Record) A row of a relation. Domain The set of allowable values for one or more attributes Degree The degree of a relation is the number of attributes it contains. Super Key An attribute or set of attributes that uniquely identifies a tuple within a relation. Candidate Key Minimal superkey such that no proper subset is a superkey within the relation.

Primary Key The candidate key that is selected to identify tuples uniquely within the relation. Foreign Key An attribute within one relation that matches the candidate key of some (possibly the same) relation. NULL Represents a value for an attribute that is unknown at the present time or is not applicable for this tuple. Integrity Constraints Used to ensure accuracy and consistency of data in a relational database. Entity Integrity Constraint Ensures that no attribute of a primary key can be null. Referential Integrity constraint Ensures that if a foreign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in its home relation or the foreign key value must be wholly null. View A virtual or derived relation that is dynamically created from the under- lying base relation(s) when required. Domain Constraint Value of each attribute in tuple must be from the same domain as the attribute Primary Key Constraint Two tuples in any relation cannot have identical values for all attributes in key Cardinality Number of tuples the table contains Properties of relations - Each relation has a unique name in the relation scheme - Each attribute has a unique name within a relation - There are no duplicate tuples - Attributes and tuples are not ordered Constraint A given column or a group of columns bust be unique in all rows in the table

Relations derived from Relationship types - Relationship types can be derived as new relations o Many-to-many relationship types -

Relationship types can be expressed as foreign keys o One-to-many relationship types

-

Relationship types can lead to merging relationships o One-to-one relationship types

Normalization Normalization is a process to ensure that the relations that support the data requirements of the use case also minimize redundancy Redundancy The state of being not or no longer needed or useful (e.g. data is represented in the database more than once). Relations with data redundancy suffer from update anomalies, which can be classified as insertion, deletion, and modification anomalies.

The major aim of relational database design is to group attributes into relations to minimize data redundancy.

Uniqueness The values of an attribute uniquely identify each tuple in a particular relation. Irreducible A key is irreducible if no proper subset of the key is unique. Determinant The determinant of a dependency refers to the attribute, or group of attributes, on the lefthand side of the arrow. Proper subset A proper subset of a set of attributes is group of attributes that is contained in the original set of attributes, excludes at least one of the original attributes Functional Dependency An attribute (A), or set of attributes, is reliant on another (B), and if and only if each A value is associated with precisely one B value. It is written as A → B.

Fully Functionally Dependent An attribute is fully functionally dependent on a set of attributes A if it is both functionally dependent on A, and not functionally dependent on any proper subset of A.

Partial Dependency An attribute is partially dependent if it is a non-key attribute and is functionally dependent on a proper subset of candidate or primary key. Transitive Dependency A transitive dependency is an indirect functional dependency, one in which A→C only by virtue of A→B and B→C. Multi-valued Dependency A dependency between attributes (for example, A, B, and C) in a relation, such that for each value of A there is a set of values for B and a set of values for C. However, the set of values for B and C are independent of each other.

Normal Form A normal form is a defined structure for relational databases in which a relation may not be nested within another relation which have attributes with particular relationships. 0NF (Un-normalised form) A relation that contains one or more repeating groups (the un-normalised form). Repeating Group Set of logically related attributes that occur multiple times in one tuple. 1NF A relation in which the intersection of each row and column contains one and only one value. 2NF A relation that is in 1NF and every non-primary-key attribute is fully functionally dependent on the primary key. 3NF A relation that is in 1NF and 2NF in which no non-primary-key attribute is transitively dependent on the primary key. 4NF A relation that is in BCNF and does not contain nontrivial multi-valued dependencies.

BCNF (Boyce-Codd Normal Form) A relation that is in 3NF and every determinant is a candidate key. Horizontal Partitioning Technique used in denormalization where tuples of a relation are spread across a number of smaller relations. 1NF Normal Forms No attributes are repeated, only one value for each type for each attribute 2NF Normal Forms All attributes can only be retrieved by the entire primary Key 3NF Normal Forms No attribute can be retrieved from another attribute that is not a primary Key. Creating a database 1. Create Database 2. Identify tables and table names (relations) 3. Identify columns and their data types (attributes and domains) 4. Identify Constraints 5. Create tables 6. Populate the tables with data Integrity Rules/Database Constraints Data must be accurate, correct and valid. Entity Integrity Rule Enforced by create a Primary key of the table, must be unique and not null for each row Referential Integrity Rule Enforced by creating a Foreign Key of the table, the value must reference to an existing row in another table CHECK Constraint Used to limit the value range that can be placed in a column. It applies to only a single column. NOTE: CHECK constraint is parsed (interpreted by MySQL) but ignored in MySQL 5.7. It is recommended that you use triggers instead.

UNIQUE Constraint The UNIQUE constraint ensures that all values in the column must be distinct. An error will occur if you try to add a new row with a value that matches an existing row. This constraint can be applied to a combination of columns, in this case some of the columns can contain null values as long as the combination of values is unique.

DEFAULT Constraint The DEFAULT constraint is used to insert a default value into a column. The value will be added to all new rows, if no other value is specified. NOT NULL Constraint The NOT NULL constraint prevents a column from accepting NULL values. This means that you cannot insert a new row or update a row without adding a value to the NOT NULL field. ON UPDATE RESTRICT Referential action. Rejects the update operation for the parent table. This is the default action and is the same as omitting the ON UPDATE clause. ON UPDATE CASCADE Referential action. Update the row from the parent table, and automatically update the matching rows in the child table. ON UPDATE SET NULL Referential action. Update the row from the parent table, and set the foreign key column or columns in the child table to NULL. ON UPDATE NO ACTION Referential action. This is the same as using ON UPDATE RESTRICT in MySQL. ON DELETE RESTRICT Referential action. Rejects the delete operation for the parent table. This is the default action and is the same as omitting the ON DELETE clause. ON DELETE CASCADE Referential action. Delete the row from the parent table, and automatically delete the matching rows in the child table. ON DELETE SET NULL Referential action. Delete the row from the parent table, and set the foreign key column or columns in the child table to NULL. ON DELETE NO ACTION Referential action. This is the same as using ON DELETE RESTRICT in MySQL.

INDEX An index is a structure that is created for fast data retrievals in a query. By default an index will always be created on primary key columns. You can create additional indexes on other table columns using CREATE INDEX command. Indexes slow down database operations so care is needed in creating indexes. VIEW Virtual table that does not necessarily actually exist in the database but is produced upon request, at time of request AUTO_INCREMENT Auto increment is a DBMS specific mechanism. The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows. The value of the column is inserted automatically every time a new record is inserted. Data Manipulation Language Allows users to insert, update, delete, view and retrieve data from the database. The part of a DML that involves data retrieval is called a query language.

Data Control Language (DCL) Used to set up privileges and roles. SELECT statement SELECT statement is used to express a query. The clause identifies the columns and/or calculated data that will appear in the result table. All column names that appear in the SELECT clause must have their corresponding tables or views listed in the FROM clause. WHERE clause selects rows to be included in the result table by applying a search condition to the rows of the named table(s). CREATE Used to create a database object such as a database, table or view. ALTER Used to alter (modify) an existing database object. Constraints SQL constraints are used to specify rules for the data in a table. If there is any violation between the constraint and the data action, the action is aborted by the constraint. ON DELETE RESTRICT

Referential action. Rejects the delete operation for the parent table. This is the default action and is the same as omitting the ON DELETE clause. ON DELETE CASCADE Referential action. Delete the row from the parent table, and automatically delete the matching rows in the child table. ON DELETE SET NULL Referential action. Delete the row from the parent table, and set the foreign key column or columns in the child table to NULL. ON DELETE NO ACTION Referential action. This is the same as using ON DELETE RESTRICT in MySQL. INDEX An index is a structure that is created for fast data retrievals in a query. By default an index will always be created on primary key columns. You can create additional indexes on other table columns using CREATE INDEX command. Indexes slow down database operations so care is needed in creating indexes. INNER JOIN Selects records that have matching values in both tables. LEFT (outer) JOIN The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match. RIGHT (outer) JOIN The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match. Database security The mechanisms that protect the database against intentional or accidental threats. Threats Any situation or event, whether intentional or accidental, that may adversely affect a system and consequently the organization.

Authorisation

The granting of a right or privilege that enables a subject to have legitimate access to a system or a system’s object. Authentication A mechanism that determines whether a user is who he or she cl...