Exam 1 440

Transitive dependency

A dependency between two nonkey attributes.

One-to-many relationship (1:M)

Between two entities in which an instance of one entity can be related to many instances of a related entity. A weak entity must have a partial key attribute.

CREATE TABLE • Used for creating and connecting relational tables

CREATE TABLE <tablename> ( column1 name, data type, and possible column constraints, column2 name, data type, and possible column constraints column details );

In SQL, how do you create a 1:M relationship between Foo and Bar, where there can be many Bar entities in relationships with a given Foo entity.

CREATE TABLE FOO ( id INT PRIMARY KEY ); CREATE TABLE BAR ( id INT PRIMARY KEY fooid INT FOREIGN KEY REFERENCES FOO(id) );

IN SQL, how do you create at 1:1 optional relationship between Foo and Bar.

CREATE TABLE Foo ( id INT PRIMARY KEY ); CREATE TABLE Bar ( id iNT PRIMARY KEY, barid INT UNIQUE FOREIGN KEY REFERENCES Foo(id) );

Regular Attribute

Column represented with just the name of the attribute.

Data Control Language (DCL)

Commands that control a database, including administering privileges and committing data. Used for data access control.

Associative Entities

Composed of the primary key attributes of each parent entity. Used to represent an M:N relationship between two or more entities. An entity type that associates the instances of one or more entity types and contains attributes that are peculiar to the relationship between those entity instances.

Database System (Information System)

Computer-based system whose purpose is to enable an efficient interaction between the users and the information captured in a database

Data Manipulation Language (DML)

DBMS language that changes database content, including data element creations, updates, insertions, and deletions. Used to insert, modify, delete and retrieve data. Example commands: INSERT INTO UPDATE DELETE SELECT

Relational Database

Database of tables with information on a specific subject. Collection of related relations within which each relation has a unique name.

Database Use

Fifth step. The insertion, modification, deletion and retrieval of the data in the database system.

Augmented Functional Dependencies

Functional dependency that contains an existing functional dependency. For example if a functional dependency: A → B exists in a relation, then: A, C → B is an augmented functional dependency Does not add new information to what is already described by the existing functional dependency It can be omitted

In SQL, how would you insert a new record into the Department table, assuming it has a single column called Name?

INSERT INTO Department (Name) VALUES ('PCSE')

Unique attribute

If an entity has a single unique attribute, then that attribute becomes the primary key in the resulting mapped relation. An entity whose only unique attribute is a composite attribute is mapped as a relation with a composite primary key Attribute whose value is different for each entity instance. Every regular entity must have at least one unique attribute. Unique attribute from the owner entity uniquely identifies every instance of the weak entity via an identifying relationship

Front-end applications developers

In charge of creating the front-end applications.

Normalization Exceptions

In general, database relations are normalized to 3NF in order to eliminate unnecessary data redundancy and avoid update anomalies However, normalization to 3NF should be done judiciously and pragmatically, which may in some cases call for deliberately not normalizing certain relations to 3NF

Exact Minimum and Maximum Cardinalities

In some cases the exact minimum and/or maximum cardinality in relationships is known in advance. Exact minimum/and or maximum cardinalities can be shown in ER diagrams.

Streamlining Functional Dependencies

Not all functional dependencies need to be depicted. The following types of functional dependencies can be omitted: Trivial Functional Dependencies Augmented Functional Dependencies Equivalent Functional Dependencies

Which SQL keyword(s) are used to sort the result set of a query?

ORDER BY

Mapping ER into Relational Schemas

Once an ER diagram is constructed, it is subsequently mapped into a relational schema (collection of relations)

Developing Front-End Applications

Refers to designing and creating applications for indirect use by the end users. Third step.

In SQL, how do you select all records from a table called Person where the FirstName column value is 'Mike' and the LastName value is 'Johnson'?

SELECT * FROM PERSON WHERE FirstName = 'Mike' AND LastName = 'Johnson'

Select

SELECT <columns, expressions> FROM <tables> WHERE <row selection condition> GROUP BY <grouping columns> HAVING <group selection condition> ORDER BY <sorting columns, expressions>

DISTINCT

SELECT DISTINCT vendorid FROM product;

Table result

SELECT productid, productname, productprice, vendorid, categoryid FROM product;

Table result showing ProductPrice increased by 10%

SELECT productid, productprice, productprice * 1.1 FROM product;

Multiple Relationships between the Same Entities

Same entities in an ER diagram can be related via more than one relationship.

Partial Functional Dependencies

Second Normal Form (2NF) A table is in 2NF if it is in 1NF and if it does not contain partial functional dependencies If a relation has a single-column primary key, then there is no possibility of partial functional dependencies

Database Modeling

Second step. Creation of the database model that is implementable by the DBMS software

Operational Databases

Support OLTP. Store detailed data to support business processes and operations. Most decision-support data are based on historical data obtained from operational databases.

Attribute (field, column)

The data elements associated with an entity. The columns in each table contain the attributes

Information

The data that is accessed by a user for some particular purpose.

Database Implementation

The steps required to change a conceptual design to a functioning database. Third step.

INSERT INTO

adds a new row to a table

Composite attribute

an attribute that can be broken down into smaller parts.

Partial Nonkey Dependency

between two or more nonkey attributes.

When a regular entity type contains a multivalued attribute, one must:....

create two new relations, one containing the multivalued attribute

Second Normal Form

relation that contains no multivalued attributes and has nonkey attributes solely dependent on the primary key but contains transitive dependencies.

Row (Tuple, Record)

represents a specific occurrence of the entity (i.e. a specific employee)

Analytical Databases

"data warehouse", condensed, summarized copy of all operational databases, includes data from outside databases as well, updated occasionally form operational databases, used for data mining. Data "play ground". Where business intelligence happens. Store data and information extracted from operational databases. These databases consist of summarized data used primarily by managers in an organization. Focus primarily on storing historical data and business metrics used exclusively for tactical or strategic decision making.

Relational table

A data table in a relational database management system. A collection of all instances that share the same set of attributes. Must not contain a repeating group.

Primary Key

A field (or group of fields) that uniquely identifies a given entity or record in a table. Column (or a set of columns) whose value is unique for each row. Each relation must have a primary key. The name of the primary key column is underlined in order to distinguish it from the other columns in the relation.

Referential Integrity Constraint

A rule that states that either each foreign key value must match a primary key value in another relation or the foreign key value must be null. In each row of a relation containing a foreign key, the value of the foreign key EITHER matches one of the values in the primary key column of the referred relation OR the value of the foreign key is null (empty). A rule that defines values that are valid for use in foreign keys.

Third Normal Form (3NF)

A table is in 3NF if it is in 2NF and if it does not contain transitive functional dependencies

Identifying Relationships

Accurately labels the way data points relate to each. Links strong entities to weak entities. Relationship between a weak entity and its owner entity in which each instance of a weak entity is associated with exactly one instance of an owner entity.

Regular entity

An entity that is existence-independent, that is, it can exist apart from all of its related entities. Strong entity. Must have at least one unique attribute.

Strong entity

An entity that is existence-independent, that is, it can exist apart from all of its related entities. Also called a regular entity.

Multivalued

Attribute for which instances of an entity can have multiple values for the same attribute.

Composite unique attribute

Attribute that is composed of several attributes and whose value is different for each entity instance

Composite Unique attribute

Attribute that is composed of several attributes and whose value is different for each entity instance.

Unique Attribute

Attribute whose value is different for each entity instance. Every regular entity must have at least one unique attribute

Spatial join

Can be used instead of a regular attribute join when the two tables do not have a common field

Modification Anomaly

Changing data in a row forces changes to other rows because of duplication. Occurs when, in order to modify one real-world value, the same modification has to be made multiple times.

Metadata

Data that describes the structure and the properties of the data. Essential for the proper understanding and use of the data.

People involved with Database Systems

Database Analysts, Designers, and Developers Front-End Applications Analysts and Developers Database Administrators o Database End Users

Database Scope

Databases can vary in their scope from small single-user (personal) databases to large enterprise databases that can be used by thousands of end-users ▪ Regardless of their scope, all databases go through the same fundamental development steps (requirements, modeling, implementation, deployment, use, etc.)

Relationships

ER modeling construct depicting how entities are related. Within an ER diagram, each entity must be related to at least one other entity via a relationship.

Direct Interaction

End-user communicating with the database directly through DBMS.

Indirect Interaction

End-user communicating with the database through front-end applications

ER Modeling Components

Entities, Relationships, and Attributes

Database Deployment

Fourth step. Releasing the database system for use by the end users.

Relationship Attributes

In some cases M:N relationships can actually have attributes of their own.

Update Operations

Insert Operation Delete Operation Modify Operation Update operation as a collective term for insert, delete and modify operations Update operation as a synonym for the modify operation

Update Anomalies

Insertion Anomaly Deletion Anomaly Modification Anomaly

Database designers (a.k.a. database modelers or architects)

Involved in the database modeling stage.

Database analysts

Involved in the requirements collection, definition, and visualization stage.

Full Key Functional Dependencies

Occurs when a primary key functionally determines the column of a relation and no separate component of the primary key partially determines the same column If a relation has a single component (non-composite) primary key, the primary key fully functionally determines all the other columns of a relation If a relation has a composite key, and portions of the key partially determine columns of a relation, then the primary key does not fully functionally determine the partially determined columns

Trivial Functional Dependencies

Occurs when an attribute (or a set of attributes) functionally determines itself or its subset. For example: A → A A, B → A, B A, B → A CampaignMgrID, CampaignMgrName → CampaignMgrName Trivial functional dependencies are not depicted

Functional Dependencies

Occurs when the value of one (or more) column(s) in each record of a relation uniquely determines the value of another column in that same record of the relation. A → B ClientID → ClientName Functional Dependency Notation Streamlining Functional Dependencies Types of Functional Dependencies

Minimum Cardinality (Participation) (Relationships)

Optional (represented by a circular symbol: 0) Mandatory (represented by a straight bar: I)

Database Administrators

Perform the tasks related to the maintenance and administration of a database system.

Database Administration and Maintenance

Performing activities that support the database end user, including dealing with technical issues. Fifth step.

Relation

Table in a relational database In order for a table to be a relation the following conditions must hold Within one table, each column must have a unique name. Within one table, each row must be unique. All values in each column must be from the same (predefined) domain. Within each row, each value in each column must be single valued (one value from a predefined domain, within each row in each column). Two additional properties of each table Order of columns is irrelevant Order of rows is irrelevant.

First Normal Form (1NF)

The first stage in the normalization process. It describes a relation depicted in tabular format, with no repeating groups and a primary key identified. All nonkey attributes in the relation are dependent on the primary key. A table is in 1NF if each row is unique and no column in any row contains multiple values. 1NF states that each value in each column of a table must be a single value from the domain of the column. Every relational table is, by definition, in 1NF. Involves eliminating groups of related multi-valued columns

Delete Operation

The operation that removes all content from a cell or from a selected cell range. used for removing data from the relation

Normalization

The process of applying rules to a database design to ensure that information is divided into the appropriate tables. First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) Other Normal Forms E-R is a topdown methodology Normalisation is a bottom-up methodology Highly formal Looks at relationships in data Tries to eliminate "undesirable dependencies" Leads to high degrees of "data independence" As normalization proceeds, the relations become progressively more restricted (stronger) in format and also less vulnerable to update anomalies

Composite attributes

Use only their simple, component attributes Attributes that consist of multiple subattributes. Make it easier to facilitate detailed queries. Not an additional attribute of an entity. Its purpose is to indicate a situation in which a collection of attributes has an additional meaning, besides the individual meanings of each attribute

End Users

Users using a database system to support their tasks and processes. Use direct or indirect interaction.

Insertion anomaly

What if new project is negotiated when there is no employee assigned to it yet? What if new employee joins who is not yet assigned to any project? Adding new rows forces user to create duplicate data. occurs when inserting data about one real-world entity requires inserting data about another real-world entity.

Multiple unique attributes (candidate keys)

When an entity has more than one unique attribute each unique attribute is also called a candidate key

Transitive Functional Dependencies

When changing a non-key column, might cause any of the other non-key columns to change

Visualizing and Structuring the Database Requirements with ER Modeling

When depicting multiple ER diagrams, each diagram should be visualized separately. Instead of multiple ER diagrams in one schema a better choice is to present each ER diagram separately. ER modeling provides a straightforward technique for collecting, structuring, and visualizing requirements

Entity Integrity Rule?

No primary key attribute may be null

Equivalent Functional Dependencies

Occurs when two columns (or sets of columns) that functionally determine each other determine other columns. If one of the equivalent functional dependencies is depicted, the other equivalent functional dependency can be omitted For example if functional dependencies : A → B B → A exists in a relation, then : A → B B → A are equivalent functional dependencies, and: A → B, X B → A, X are equivalent functional dependencies, and: Y,A → B, X Y,B → A, X are equivalent functional dependencies

Maximum Cardinality (Relationships)

One (represented by a straight bar: I). Many (represented by a crow's foot symbol).

Maximum Cardinality-Wise

One-to-one relationship (1:1) One-to-many relationship (1:M) Many-to-many relationship (M:N)

Entities

Regular, weak, or associative. Constructs that represent what the database keeps track of. The basic building blocks of an ER diagram. Represent various real world notions, such as people, places, objects, events, items, and other concepts. Within one ERD each entity must have a different name.

Binary relationship

Relationship between two entities (degree 2 relationship)

Ternary relationship

Relationship involving three entities (degree 3 relationship).

Cascading delete

Removes all records in other tables associated with the record to be deleted.

Denormalization

Reversing the effect of normalization by joining normalized relations into a relation that is not normalized, in order to improve query performance The data that resided in fewer relations prior to normalization is spread out across more relations after normalization This has an effect on the performance of data retrievals Can be used in dealing with the normalization vs. performance issue Not a default process that is to be undertaken in all circumstances Instead, denormalization should be used judiciously, after analyzing its costs and benefits

Referential Integrity Constraint

Rule that states that each foreign key value must match a primary key value in the other relation.

In SQL, how do you select all columns from a table named Pokemon?

SELECT * FROM POKEMON

SELECT • Used for the retrieval of data from the database relations

SELECT <columns> FROM <table>

In SQL, how do you select all values for the column Name from the table Person?

SELECT NAME FROM PERSON

Data formats

Text, numbers, figures, graphics, images, audio/video recordings and more

Cardinality Constraints (Relationships)

Show how many instances of one entity can be associated with instances of another entity. Maximum or minimum.

DROP TABLE

A SQL command used to delete database objects such as tables, views, indexes, and users.

In SQL, how do you delete all rows from a table called "Student"?

DELETE FROM STUDENT

DROP TABLE • Used to remove a table from the database

DROP TABLE <tablename>;

SQL Commands Categories

Data Definition Language (DDL) Data Manipulation Language (DML) Data Control Language (DCL) Transaction Control Language (TCL)

Attributes

Description of a characteristic of an entity Represents the details that will be recorded for each entity instance Within one entity, each attribute must have a different name. Regular Unique Multiple Unique (Candidate Keys) Composite Composite Unique Derived Multivalued Optional Relationship Attributes

Other Normal Forms

(1) Boyce-Codd Normal Form- remove remaining anomalies resulting from functional dependencies (2) Fourth Normal Form- remove anomalies that result from a multi-valued dependencies (3) Fifth Normal Form- designed to cope with dependency known as join dependency

Foreign keys

-A composite foreign key must be created using a table constraint in SQL. -A composite foreign key cannot be created using a column constraint in SQL. (Ex. Constraints - NOT NULL, UNIQUE, PK, FK) -A composite foreign key is necessary to reference a table with a composite primary key. -A composite foreign key must be created using a table constraint in SQL.

List every relational database property provided by Entity Integrity.

-A primary key must not be null. -A primary key must have a unique value within a table.

List every requirement of First Normal Form.

-Data must be in a table with named columns. -Each row must have no more than one value for each column.

Select every requirement of Second Normal Form.

-Data must be in a table with named columns. -There must be no partial dependencies -Each row must have no more than one value for each column.

Third Normal Form.

-Each row must have no more than one value for each column. -Data must be in at table with named columns. -There must be no transitive dependencies. -There must be no partial dependencies.

List all relational database properties provided by Referential Integrity.

-If a foreign key has a non-null value, it must match a primary key value in the referenced table.

Steps in the Development of Database Systems

1. Requirements collection, definition, and visualization 2. Database modeling (logical database modeling) 3. Database implementation or Developing front-end applications 4.Database deployment 5. Database Use or admin and maintenance

ORDER BY

A SQL clause that is useful for ordering the output of a SELECT query (for example, in ascending or descending order).

partial dependency

A dependency between a nonkey attribute and part of a primary key.

Foreign Key

A primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables. Column in a relation that refers to a primary key column in another (referred) relation. A mechanism that is used to depict relationships in the relational database model. For every occurrence of a foreign key, the relational schema contains a line pointing from the foreign key to the corresponding primary key

Third Normal Form (3NF)

A relation that is in second normal form and has no transitive dependencies.

Common field

A relational database establishes the relationships between entities by means of a

Unary relationship

A relationship between the instances of a single entity

Unary relationship

A relationship between the instances of a single entity type.

Unary relationship (recursive relationship)

A relationship between the instances of one entity type. Occurs when an entity is involved in a relationship with itself (degree 1 relationship).

Degrees (Relationships)

A relationship between two entities is a degree 2 relationship). Reflects how many entities are involved in the relationship

Ternary relationship

A simultaneous relationship among the instances of three entity types.

Relationship Roles

Additional syntax that can be used in ER diagrams at the discretion of a data modeler to clarify the role of each entity in a relationship.

Derived attribute

An attribute that can be calculated form related attribute values.

Derived attribute

An attribute that does not physically exist within the entity. Its values are calculated and not permanently stored in a database. For example, the Age attribute might be derived by subtracting the birth date from the current date.

Optional Attribute

An attribute that may not have a value for every entity (or relationship) instance with which it is associated. Attribute that is allowed to not have a value. Can be left empty.

Required attribute

An attribute that must have a value for every entity (or relationship) instance.

Associative entity

An entity that associates the instances of one or more entity types and contains attributes specific to the relationships.

Weak entity

An entity that displays existence dependence and inherits the primary key of its parent entity. For example, a DEPENDENT requires the existence of an EMPLOYEE. Does not have a unique attribute of its own.

Weak entity

An entity type whose existence depends on another entity type.

Determinant

Attribute on the left-hand side of the arrow in a functional dependency.

One-to-one relationship (1:1)

Between two entities in which an instance of one entity can be related to only one instance of a related entity. A weak entity doesn't need to have a partial key attribute.

Many-to-many relationship (M:N)

Between two entities in which an instance of one entity is related to many instances of another and one instance of the other can be related to many instances of the first entity. Can actually have attributes of their own.

SQL Commands and Keywords

CREATE TABLE DROP TABLE INSERT INTO SELECT WHERE DISTINCT ORDER BY LIKE Aggregate Functions (COUNT, SUM, AVG, MIN and MAX)

In SQL, what is used to make table definitions?

CREATE TABLE _______

Physical schema

Contains the specifications of how data from a conceptual schema are stored in a computer's secondary memory.

Data Definition Language (DDL)

DBMS language that builds the data dictionary, creates the database, describes logical views, and specifies record or field security constraints Used to create and modify the structure of the database. Example commands: CREATE ALTER DROP

Deletion Anomaly

Deleting rows may cause a loss of data that would be needed for other future rows. What if Judy resigns from her job? Occurs when deletion of data about a real-world entity forces deletion of data about another real-world entity.

ER Modeling vs. Normalization

ER modeling followed by mapping into a relational schema is one of the most common database design methods. When faced with a non-normalized table, instead of identifying functional dependencies and going through normalization to 2NF and 3NF, a designer can analyze the table and create an ER diagram based on it (and subsequently map it into a relational schema)

false

Every relation without transitive functional dependencies is in 2NF. Every relation without transitive functional dependencies is in 3NF.

Data

Facts that are recorded and can be accessed. Recorded and kept because it is considered to be of use to an intended user.

Requirements Collection, Definition, and Visualization

First step. Conceptual Modeling.

Data model

Graphical system used to capture the nature and relationships among data.

INSERT INTO

INSERT INTO <tablename> VALUES (value 1, value 2, ... );

INSERT INTO

INSERT INTO <tablename>(columnname, columnname, . . .) VALUES (value, value, . . .);

INSERT INTO

INSERT INTO includes(quantity, tid, productid) VALUES (1, 'T555', '6X6');

referential integrity constraint lines

In a relational schema lines pointing from the foreign key to the corresponding primary key are referred to as

Entity Integrity Constraint

In a relational table, no primary key column can have null (empty) values. A rule stating that no primary key column can be optional. Every RDBMS enforces this rule

Front-end application analysts

In charge of collecting and defining requirements for front-end applications.

Database developers

In charge of implementing the database model as a functioning database using the DBMS software.

Relational Database Model

Logical database model that represents a database as a collection of two dimensional related tables.

Multivalued attribute

May take on more than one value for a particular entity instance

Types of Functional Dependencies

The functional dependencies that are used as a basis for the typical normalization process can be classified in one of the three categories Partial functional dependency Full key functional dependency Transitive functional dependency

The Next Version of the Database

The new version of the database follows the same development steps as the initial version

Second Normal Form (2NF)

The second stage in the normalization process, in which a relation is in 1NF and there are no partial dependencies (dependencies in only part of the primary key). A table is in 2NF if it is in 1NF and if it does not contain partial functional dependencies If a relation has a single-column primary key, then there is no possibility of partial functional dependencies Such a relation is automatically in 2NF and it does not have to be normalized to 2NF If a relation with a composite primary key has partial dependencies, then it is not in 2NF, and it has to be normalized it to 2NF

In SQL, how can you change 'Mike' to 'Michael' in the FirstName column in the Person table?

UPDATE TABLE Person SET FirstName = 'Michael' WHERE FirstName = 'Mike';

Database End Users

Use a database system to support their work- or life-related tasks and processes. Users differ in Level of technical sophistication Amount of data that they need Frequency with which they access the database system

Transaction Control Language (TCL)

Used for managing database transactions

CREATE TABLE

Valid SQL command for constructing a table.

Relational Schema

Visual depiction of the relational database model. Once database requirements are collected and visualized as an ER diagram, the next step in creating a relational database is to map (convert) the ER diagram into a relational schema

Multiple Unique (Candidate Keys)

When an entity has more than one unique attribute each unique attribute is also called a candidate key

Identifying relationship

a relationship between a weak entity type and its owner.

Partial Dependency

one or more nonkey attributes are functionally dependent on part, but not all, of the primary key.

Composite key

primary key that consists of more than one attribute

Modify Operation

used for changing the existing data in the relation

Insert Operation

used for entering new data in the relation