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