Intro To Database Systems - Test 1

What *6* steps are required to develop an ER Diagram? 4.6

1.) Create a detailed narrative of the organization's description of operations. 2.) Identify the business rules based on the description of operations. 3.) Identify the main entities and relationship from the business rules. 4.) Develop the initial ERD. 5.) Identify the attributes and primary keys that adequately describe the entities. 6.) Revise and review the ERD.

What are the 9 functions of Database Management System (DBMS)? 1.5

1.) Data Dictionary Management 2.) Data Storage Management 3.) Data Transformation and Presentation 4.) Security Management 5.) Multi-User Access Control 6.) Backup and Recovery Management 7.) Data Integrity Management 8.) Database Access Languages and Application Program Interfaces 9.) Database Communication Interfaces

What is Entity Integrity? 3.4

1.) Each row (entity instance) in a table should have it's own unique identity. 2.) Two requirements of a Primary Key - All values in the primary key must be unique. - No key attribute in the primary key may contain a null.

A database is a shared, integrated computer structure that stores a collection of: (2 things) 1.1

1.) End-User data, that is, raw facts of interest to the end user. 2.) Metadata, or data about data, through which the end-user data are integrated and managed -The metadata provides a description of the data characteristics and the set of relationships that links the data found within the database-

What are the *4* Data Model Basic Building Blocks? 2.2

1.) Entities 2.) Attributes 3.) Relationships 4.) Constraints

What does an Attribute entail? 2.2

An attribute is a characteristic of an entity. For example, a CUSTOMER entity would be described by attributes such as customer last name, customer first name, customer phone number, customer address, and customer credit limit. Attributes are the equivalent of fields in file systems.

What is a Superkey? 3.3

An attribute that uniquely identifies each entity in the table.

What is Divide used for? 3.5

Divide is used to answer questions about on set of data being associated with all values of data in another set. - The Divide operation uses one single-column table as the divisor and one 2-column table as the dividend. - The tables must have a common column. - The output of the Divide operation is a single column with the values of the column being from the dividend table rows where the value of the common columns in both tables matched.

What are the *3* types of relationships? 2.3

one to one (1:1 or 1 .. 1) , one to many (1:M or 1 ..*), many to many (M:N or * .. *)

What is a Multi-Valued Attribute? 4.3

- A Multi-Valued Attribute can have (you guessed it) many values. - You can get rid of a multi-valued attribute by: 1.) Within the original entity, create several new attributes, one for each of the original multi-valued attributes components *OR* 2.) Create a new entity composed of the original multi-valued attributes components

What is an Entity? 4.2

- An entity is an object of interest to the user. - It is a table. - It is represented by a rectangle with the name inside. The name is written in capital letters.

What is an attribute? 4.3

- Attributes are characteristics of entities. - It is a column in a table. - the name in a column is the attribute.

What is the Crow's foot symbol with two straight lines (||) and what is the cardinality of it? 4.1

- One and only one, 1 side is *mandatory* - The Cardinality is (1,1).

What is the Crow's foot symbol with a straight line and three lines coming out of it and what is the cardinality of it? 4.1

- One or many, the many side is *mandatory*. - The Cardinality is (1,N).

What is Product used for? 3.5

- Product yields all the possible pairs of rows from two tables. -Also known as the Cartesian Product

What is the Entity Relationship Diagram (ERD)? 4.1

- The ERD represents the conceptual database as viewed by the end-user. - ERD's depict the database's main components: entities, attributes, and relationships. - The two most commonly used models are the Chen Model and the Crow's Foot Model

What is a Physical Model? 2.9

A model in which physical characteristics such as location, path, and format are described for the data.

What is Database Design? 1.6

Database design is the activities that focus on the design of the database structure that will be used to store and manage end-user data.

What is Difference used for? 3.5

Difference yields only the rows that appear in one table and not the other.

What is the command to insert content for a row of a table? 5.4

INSERT INTO -name of the table- VALUES (...);

What is Project used for? 3.5

Project yields a vertical subset of a table.

What is Security Management used for? 1.5

The DBMS creates a security system that enforces user security and data privacy. Security rules determine which users can access the database, which data items each user can access, and which data operations (read, add, delete, or modify) the user can perform. This is especially important in multi-user database systems. All database users may be authenticated to the DBMS through a username and password or through bio-metric authentication such as a fingerprint scan. The DBMS uses this information to assign access privileges to various database components such as queries and reports

What is Data Integrity Management used for? 1.5

The DBMS promotes and enforces integrity rules, thus minimizing data redundancy and maximizing data consistency. The data relationships stored in the data dictionary are used to enforce data integrity. Ensuring data integrity is especially important in transaction-oriented database systems.

What is Multi-User Access Control used for? 1.5

To provide data integrity and data consistency, the DBMS uses sophisticated algorithms to ensure that multiple users can access the database concurrently without compromising its integrity. Chapter 10, Transaction Management and Concurrency Control, covers the details of multi-user access control.

What is Union used for? 3.5

Union combines all rows from the two tables, excluding duplicate rows.

What do Connectivity and Cardinality express?

- The connectivity is the classification of relationship between entities. This classification includes 1:1, 1:M, M:N -A property that assigns a specific value to connectivity and expresses the range of allowed entity occurrences associated with a single occurrence of the related entity.

What is the Crow's foot symbol with a circle and a line coming out of it that is crossed (♀) and what is the cardinality of it? 4.1

- Zero or one, 1 side is optional - The Cardinality is (0,N).

What is the Crow's foot symbol with a circle and three lines coming out of it and what is the cardinality of it? 4.1

- Zero to many, the many side is optional. - The Cardinality is (0,N).

What is a Table? 3.1

-A table is a two-dimensional structure composed of rows and columns. -A table is also called a relation.

What are the *2* types of Dependence's? 3.2

1.) *Determinant* - Any attribute in a specific row whose value directly determines other values in that row. 2.) *Dependent* - Any attribute whose value is determined by another attribute.

What is a Weak Entity? 4.2

1.) A weak entity meets the following two conditions: - The entity is existence dependent. - The entity has a primary key that is partially or totally derived from the parent entity in the relationship. 2.) Weak entity representation - In the Chen Notation, the weak entity is represented by using a double-walled entity rectangle. - In the Crow's Foot Notation, it uses the relationship line and the PK/FK designation to indicate whether the related entity is weak.

What are the *2* Character Data Types? 5.5

1.) CHAR(L) - Fixed length character data for up to 255 characters. 2.) VARCHAR(L) or VARCHAR2(L) - Variable length character data. The designation VARCHAR2(25) will not leave unused spaces.

What are the *5* types of Attributes? 4.3

1.) Composite Attribute 2.) Simple Attribute 3.) Single-Value Attribute 4.) Multi-Valued Attributes 5.) Derived Attributes

What are the *4* Numeric Data Types? 5.5

1.) NUMBER(L, D) - where D stores the number of decimal places and L stores the length (including the sign and decimal place) 2.) INTEGER - may be abbreviated INT, Integers are (whole) counting numbers and cannot use decimal places. 3.) SMALLINT - Like INTEGER but limited to integer values up to six digits. 4.) DECIMAL(L, D) - Like the NUMBER specification, but the storage length is a minimum specification. That is, greater lengths are acceptable, but smaller ones are not. DECIMAL (9,2), DECIMAL(9), and DECIMAL are all acceptable.

What are the *6* types of Keys in a Table? 3.3

1.) Primary Key (PK) 2.) Composite Key 3.) Superkey 4.) Candidate Key 5.) Foreign Key 6.) Secondary Key

What are the *10* types of Relational Algebra? 3.5

1.) Select 2.) Project 3.) Union 4.) Intersect 5.) Difference 6.) Product 7.) Divide 8.) Natural Join 9.) Left Outer Join 10.)Right Outer Join

What are the *3* types of Relationship Degrees? 4.5

1.) Unary Relationship 2.) Binary Relationship 3.) Ternary and Higher Degree Relationship

What are the *3* types of Relationships? 4.4

1.) Weak Relationship 2.) Strong Relationship 3.) Recursive Relationship

What is the difference between a Single-User database and a Multi-User database? 1.2

A *single-user database* supports only one user at a time. In other words, if user A is using the database, users B and C must wait until user A is done. A single-user database that runs on a personal computer is called a desktop database. In contrast, a *multi-user database* supports multiple users at the same time. When the multi-user database supports a relatively small number of users (usually fewer than 50) or a specific department within an organization, it is called a *workgroup* database. When the database is used by the entire organization and supports many users (more than 50, usually hundreds) across many departments, the database is known as an *enterprise database*.

What is a Binary Relationship? 4.5

A Binary Relationship is an association between two entities. Ex. a PROFESSOR teaches one or more CLASSes

What is a Database Management System (DBMS)? 1.3

A Database Management System (DBMS) is a collection of programs that manage the database structure and controls the access to the data stored in the database.

What is Left Outer Join used for? 3.5

A Left Outer Join yields all of the rows in the first table, including those that do not have a matching value in the second table.

What is a Primary Key (PK)? 3.3

A Primary key is an attribute or combination of attributes that uniquely identifies any given row.

What does a Relationship Degree indicate? 4.5

A Relationship Degree indicates the number of entities or participants associated with a relationship.

What is Right Outer Join used for? 3.5

A Right Outer Join yields all of the rows in the second table, including those that do not have a matching value in the first table.

What is a Ternary and Higher-Degree Relationship? 4.5

A Ternary (Higher-Degree) Relationship is an association among three entities (more than three entities). Ex. • A DOCTOR writes one or more PRESCRIPTIONs. • A PATIENT may receive one or more PRESCRIPTIONs. • A DRUG may appear in one or more PRESCRIPTIONs. (To simplify this example, assume that the business rule states that each prescription contains only one drug. In short, if a doctor prescribes more than one drug, a separate prescription must be written for each drug.)

What is a Unary Relationship? 4.5

A Unary Relationship is an association within a single entity. Ex. the existence of the "manages" relationship means that EMPLOYEE requires another EMPLOYEE to be the manager—that is, EMPLOYEE has a relationship with itself. Such a relationship is known as a recursive relationship.

What is a Composite Attribute? 4.3

A composite attribute can further be subdivided to yield additional attributes.

What does a Constraint entail? 2.2

A constraint is a restriction placed on the data. Constraints are important because they help to ensure data integrity. Constraints are normally expressed in the form of rules: • An employee's salary must have values that are between 6,000 and 350,000. • A student's GPA must be between 0.00 and 4.00. • Each class must have one and only one teacher. How do you properly identify entities, attributes, relationships, and constraints? The first step is to clearly identify the business rules for the problem domain you are modeling.

What is Database Communication Interfaces used for? 1.5

A current-generation DBMS accepts end-user requests via multiple, different network environments. For example, the DBMS might provide access to the database via the Internet through the use of web browsers such as Mozilla Firefox, Google Chrome, or Microsoft Internet Explorer. In this environment, communications can be accomplished in several ways: - End users can generate answers to queries by filling in screen forms through their preferred web browser. - The DBMS can automatically publish predefined reports on a website. - The DBMS can connect to third-party systems to distribute information via email or other productivity applications.

What is a Strong Entity? 4.2

A strong entity is existence independent.

What is a Strong Relationship? 4.4

A strong relationship exists when the primary key of the related entity contains a primary key component of the parent entity.

What is a Weak Relationship? 4.4

A weak relationship exists if the primary key of the related entity does not contain a primary key component of the parent entity.

What is the difference between an Operational database and a Analytical database? 1.2

A database that is designed primarily to support a company's day-to-day operations is classified as an *operational database*, also known as an *online transaction processing (OLTP) database, transactional database*, or *production database*. In contrast, an *analytical database* focuses primarily on storing historical data and business metrics used exclusively for tactical or strategic decision making. Such analysis typically requires extensive "data massaging" (data manipulation) to produce information on which to base pricing decisions, sales forecasts, market strategies, and so on. Analytical databases allow the end user to perform advanced analysis of business data using sophisticated tools

What is the difference between a Centralized database and a Distributed database? 1.2

A database that supports data located at a single site is called a *centralized database.* A database that supports data distributed across several different sites is called a *distributed database.*

What is Referential Integrity? 3.4

A dependent table's foreign key must have either a null entry or a matching entry in the related table.

What is a Derived Attribute? 4.3

A derived attribute whose value is calculated from other attributes.

What is a Key? 3.3

A key consists of one or more attributes that determines other attributes.

What is a Composite Key? 3.3

A key that is composed of more than one attribute.

What is a Secondary Key? 3.3

A key used strictly for data retrieval.

What is a Candidate Key? 3.3

A minimal superkey. That is, a superkey without any unnecessary attributes.

What is a Query? 5.1

A query is a request for information from a database. A query is both a question and an answer.

What is a Recursive Relationship? 4.4

A recursive relationship is one in which a relationship can exist between occurrences of the same entity set.

What is Natural Join used for? 3.5

A relational operation yields a new table composed of only rows with common values in their common attributes. - First, a Product operation is performed to create a product table. - Second, a Select operation is performed on the product table to generate a selection table. - Third, a Project operation is performed on the selection table to generate the result table.

What does a Relationship entail? 2.2

A relationship describes an association among entities. For example, a relationship exists between customers and agents that can be described as follows: an agent can serve many customers, and each customer may be served by one agent. Data models use three types of relationships: one-to-many, many-to-many, and one-to-one. Database designers usually use the shorthand notations 1:M or 1..*, M:N or *..*, and 1:1 or 1..1, respectively. (Although the M:N notation is a standard label for the many-to-many relationship, the label M:M may also be used.) The following examples illustrate the distinctions among the three relationships. • *One-to-many (1:M or 1..*)* relationship. A painter creates many different paintings, but each is painted by only one painter. Thus, the painter (the "one") is related to the paintings (the "many"). Therefore, database designers label the relationship "PAINTER paints PAINTING" as 1:M. Note that entity names are often capitalized as a convention, so they are easily identified. Similarly, a customer (the "one") may generate many invoices, but each invoice (the "many") is generated by only a single customer. The "CUSTOMER generates INVOICE" relationship would also be labeled 1:M. • *Many-to-many (M:N or *..*)* relationship. An employee may learn many job skills, and each job skill may be learned by many employees. Database designers label the relationship "EMPLOYEE learns SKILL" as M:N. Similarly, a student can take many classes and each class can be taken by many students, thus yielding the M:N label for the relationship expressed by "STUDENT takes CLASS." • *One-to-one (1:1 or 1..1)* relationship. A retail company's management structure may require that each of its stores be managed by a single employee. In turn, each store manager, who is an employee, manages only a single store. Therefore, the relationship "EMPLOYEE manages STORE" is labeled 1:1. The preceding discussion identified each relationship in both directions; that is, relationships are bidirectional: • One CUSTOMER can generate many INVOICEs. • Each of the many INVOICEs is generated by only one CUSTOMER.

What is a Relationship? 4.4

A relationship is an association between entities.

What is a data model? 2.1

A relatively simple representation, usually graphical, of more complex 'real-world' data structures

What is a Simple Attribute? 4.3

A simple attribute cannot be subdivided.

What is a Single-Value Attribute? 4.3

A single value attribute can only have a single value.

What does an Entity entail? 2.2

An entity is a person, place, thing, or event about which data will be collected and stored. An entity represents a particular type of object in the real world, which means an entity is "distinguishable"—that is, each entity occurrence is unique and distinct. For example, a CUSTOMER entity would have many distinguishable customer occurrences, such as John Smith, Pedro Dinamita, and Tom Strickland. Entities may be physical objects, such as customers or products, but entities may also be abstractions, such as flight routes or musical concerts

What are Business Rules? 2.4

Business rules are the descriptions of a policy, procedure, or principle within an organization.

What is the command to create a Database? 5.2

CREATE DATABASE -name of the database-;

What is the command to create a Table? 5.3

CREATE TABLE -name of the table-

What is Intersect used for? 3.5

Intersect yields only the rows that appear in both tables.

Translate Business Rules into Data Model Components 2.5

It is a procedure of identifying entities, attributes, relationships, and constraints.

What is an External Models? 2.6

It is the application programmer's view of the data environment.

What is a Foreign Key? 3.3

It is the primary key of one table that has been placed into another table to create a common attribute.

What is an Internal Model? 2.8

It is the representation of a database as 'seen' by the DBMS.

What is a Conceptual Model? 2.7

It provides a global view of an entire database and describes the main data objects, avoiding details.

What is the role of a Database Management System (DBMS)? 1.4

It serves as the intermediary between the user and the database

What format is the DATE Data Type? 5.5

Julian Date Format.

What is Select used for? 3.5

Select yields a horizontal subset of a table.

What is Data Storage Management used for? 1.5

The DBMS creates and manages the complex structures required for data storage, thus relieving you from the difficult task of defining and programming the physical data characteristics. A modern DBMS provides storage not only for the data but for related data-entry forms or screen definitions, report definitions, data validation rules, procedural code, structures to handle video and picture formats, and so on. Data storage management is also important for database performance tuning. Performance tuning relates to the activities that make the database perform more efficiently in terms of storage and access speed. Although the user sees the database as a single data storage unit, the DBMS actually stores the database in multiple physical data files. (See Figure 1.12.) Such data files may even be stored on different storage media. Therefore, the DBMS doesn't have to wait for one disk request to finish before the next one starts. In other words, the DBMS can fulfill database requests concurrently. Data storage management and performance tuning issues are addressed in Chapter 11, Database Performance Tuning and Query Optimization.

What is Backup and Recovery Management used for? 1.5

The DBMS provides backup and data recovery to ensure data safety and integrity. Current DBMS systems provide special utilities that allow the DBA to perform routine and special backup and restore procedures. Recovery management deals with the recovery of the database after a failure, such as a bad sector in the disk or a power failure. Such capability is critical to preserving the database's integrity. Chapter 16 covers backup and recovery issues.

What is Database Access Languages and Application Program Interfaces used for? 1.5

The DBMS provides data access through a query language. A query language is a nonprocedural language—one that lets the user specify what must be done without having to specify how. Structured Query Language (SQL) is the de facto query language and data access standard supported by the majority of DBMS vendors. In addition, the DBMS provides administrative utilities used by the DBA and the database designer to create, implement, monitor, and maintain the database.

What is Data Dictionary Management used for? 1.5

The DBMS stores definitions of the data elements and their relationships (metadata) in a data dictionary. In turn, all programs that access the data in the database work through the DBMS. The DBMS uses the data dictionary to look up the required data component structures and relationships, thus relieving you from having to code such complex relationships in each program. Additionally, any changes made in a database structure are automatically recorded in the data dictionary, thereby freeing you from having to modify all of the programs that access the changed structure. In other words, the DBMS provides data abstraction, and it removes structural and data dependence from the system.

What is Data Transformation and Presentation used for? 1.5

The DBMS transforms entered data to conform to required data structures. The DBMS relieves you of the chore of distinguishing between the logical data format and the physical data format. That is, the DBMS formats the physically retrieved data to make it conform to the user's logical expectations. For example, imagine an enterprise database used by a multinational company. An end user in England would expect to enter the date July 11, 2017, as "11/07/2017." In contrast, the same date would be entered in the United States as "07/11/2017." Regardless of the data presentation format, the DBMS must manage the date in the proper format for each country