Database Systems

Réussis tes devoirs et examens dès maintenant avec Quizwiz!

File/Table Operations

1- created 2- populate -key in/import 3- updated (records)

Relation

A.k.a Table; is a matrix composed of intersecting rows and columns.

Natural Key

AKA natural identifier A real-world, generally accepted identifier used to distinguish/uniquely identify real-world objects. It is familiar to end users and forms part of their day-to-day business vocabulary. Most of these make acceptable PK identifiers. Example: SSN.

Logical Operators

AND/OR/NOT Used in conditional expressions

Database communication interfaces

Accept end-user requests via multiple, different network environments.

Manual File Systems

Accomplished through a system of file folders and filing cabinets

hardware

CPU, storage

Computerized File Systems

Data processing (DP) specialist: Created a computer based system that would track data and produce required reports

DEFAULT

Defines a default value for a column (when no value is given)

DELETE

Deletes one or more rows from a table

A simple attribute can be broken down into smaller pieces.

FALSE

What commands modify a SQL database

INSERT DELETE UPDATE

data integrity

In a relation database, a condition in which the data in the database complies with all entity and referential integrity constraints

Unstructured data

It exists in their original state

Secondary key

One field or a combination of fields for which more than one record may have the same combination of values. Also called a nonunique key

Schema

The conceptual organization of the entire database as viewed by the DBA.

Types of Data Anomaly

Update Anomalies, Insertion Anomalies, Deletion Anomalies

Simple retrieval query in SQL

can consist of up to four clauses. Mandatory clauses are SELECT and FROM. Must be in the following order: SELECT <attribute list> FROM <table list> {WHERE <condition>} {ORDER BY <attribute list>};

Constraint name

must be unique.

Index

used to logically access rows in a table quickly

data inconsistency

資料不一致

Steps for the normalization procedure:

1. Eliminate the repeating groups (present the data in a tabular format where each cell has a single value and there are no repeating groups. To eliminate repeating groups you eliminate the nulls). 2. Identify the Primary Key (make sure it is a PK that will uniquely identify ANY attribute value) 3. Identify all dependencies (know what is related and what determines what)

The Heirarchical Model

1960; represented by upside-down tree. Only 1:M relationships between records (segments).

Advantages of the DBMS

Better data integration and less data inconsistency, Increased end user productivity, Improved: Data sharing, Data security, Data access, Decision making

CREATE VIEW

Creates a dynamic subset of rows and columns from one or more tables

CREATE INDEX

Creates an index for a table

Atomic Data

Data stored at their lowest level of granularity

structured data

Data that has been formatted to facilitate storage, use, and information generation

Optional Participation

One entity occurrence does not REQUIRE a corresponding entity occurrence in a particular relationship. Example: "COURSE generates CLASS" --> some courses do not generate a class. You put a small circle (O) on the side of the optional entity.

Ch. 1 - Data

Recorded facts and numbers

NOT

Requires the first query to be true but not the second.

HAVING

Restricts the selection of grouped rows based on a condition

WHERE

Restricts the selection of rows based on a conditional expression

SUM

Returns the sum of all values for a given column

Granularity

The level of detail represented by the values stored in a table's row.

Indexed file organization

The storage of records either sequentially or nonsequentially with an index that allows software to locate individual recores

Sequential file organization

The storage of records in a file in sequence according to a primary key value

T/F? Relationships between entities always operate in both directions.

True Example: a CUSTOMER may generate many INVOICEs. & each INVOICE is generated by one CUSTOMER. (1:M relationship)

T/F? Normalization is usually used in conjunction with entity relationship modeling.

True. A DB designer will make a data model using Crow's Foot notation ERDs and then after the initial design is complete, the designer can use normalization to analyze the relationships among the attributes within each entity and determine if the structure can be improved through normalization.

Ch. 1 - Users

Use forms to read, enter, and query data, and they produce reports to convey information

Byte

Whole numbers from 0 to 255

XML database

XML資料庫

An entity that associates the instances of one or more entity types and contains attributes specific to the relationships is called a(n):

associative entity

default value

assumed value if no explicit value

A property or characteristic of an entity type that is of interest to the organization is called a(n):

attribute

composite attribute

attribute composed of several more basic/ atomic attributes (ex: Name = FirstName, MiddleInitial, Surname)

Composite Attribute

attribute that can be subdivided to yield additional attributes

atomic attribute

attribute which contains a single value of the appropriate type (relational model should have only atomic attributes) (ex: "address" of type varchar(100) should only have 1 address)

multivalued attribute

attribute which has lower and upper bounds on the number of values for an individual entry (ex: phone number = set # of digits) (relational model should not store multivalued attributes)

Relationship Strength

based on how the PK of a related entity is defined.

knowledge

body of information and facts about a specific subject

Data Definition Language

commands that create, modify, and remove database objects such as tables, indexes, and users

recent trends

data driven websites distributed databases object-oriented DB data warehouses data marts big data

Data dictionary management

defines data elements and their relationships

relational schema, R

definition of a table in the database; can be denoted by listing table name & attributes R(A1, A2, ... , An) where R = table name and each An = an attribute

The number of entity types that participate in a relationship is called the:

degree

data anomaly

develops when not all of the required changes in the redundant data are made successfully

restrict deletes

do not allow deletion of a record in the parent table if related rows exist in the child table

Attributes have a ___

domain

Strong or Regular Entity

exhibits existence independence

data redundancy

exists when the same data are stored unnecessarily at different places

Velocity

refers to real time analytics; analyze quickly

field

smallest unit of application data recognized by system software

structural independence

結構獨立性

online transaction processing (OLTP)

線上交易處理(OLTP)

data

資料

Dependency Diagram

A diagram that depicts all dependencies found within a given table structure. Its use make it less likely that you will overlook an important dependency.

Boolean Algebra

A field in math dedicated to the use of logical operators. Know how to write sql statements with and/or/not

Index

A table or other data structure used to determine in a file the location of records that satisfy some condition

IS NULL

Checks whether an attribute value is null

Relationship Degree

The number of entities or participants associated with a relationship.

structural independence

exists when you can change the file structure without affecting the application's ability to access the data

Cardinality

expresses the minimum and maximum number of entities associated with one occurrence of related entity

A student can attend five classes, each with a different professor. Each professor has 30 students. The relationship of students to professors is a ________ relationship.

many-to-many

structural dependence

結構依賴性

Structured Query Language (SQL)

結構化查詢語言(SQL)

structured data

結構化資料

NoSQL

New generation of databases that address specific challenges of the Big Data era. Not based on relational model, distributed DB architectures, high fault tolerance, very large amounts of sparse data, performance over transaction consistency.

Number

Numeric data used in mathematical calculations

DROP VIEW

Permanently deletes a view

DROP INDEX

Permanently deletes an index

Data Redundancy Implications

Poor data security, Data inconsistency, Increased likelihood of data entry errors when complex entries are made in different files, Data anomaly

Data redundancy implications

Poor data security, data inconsistency, and increased likelihood of data-entry errors when complex entries are made in different files.

Semistructured data

Processed to some extent

Denormalization

Produces a lower normal form so 3NF will be converted to 2NF. BUT: The price you pay for increased performance through denormalization is greater data redundancy.

Data quality

Promoting accuracy, validity, and timeliness of data

Data quality

Promoting accuracy, validity, and timeliness of data.

LOOK AT PAGE 249 FOR DATA MODELING CHECKLIST

READ IT FROM TEXTBOOK PDF

End User Data

Raw facts of interest to end user

data

Raw facts, or facts that have not yet been processed to reveal their meaning to the end user

Validation Rules

Reduce the possibility of error when data is entered. The rule restricts what can be entered.

Big Data

Refers to a movement to find new & better ways to manage large amounts of web-generated data and derive business insight from it. Seeks high performance, scalability, and reasonable cost.

Logical Design

Refers to the task of creating a conceptual data model that could be implemented in any DBMS.

Which of the following is NOT a good characteristic of a data name?

Relates to a technical characteristic of the system

Ch. 1 - What is the most commonly used type of database?

Relational database

ROLLBACK

Restores data to their original values

AVG

Returns the average of all values for a given column.

Desktop database

Runs on PC.

Order of precedence:

SELECT SUM/AVG/DISTINCT/COUNT FROM WHERE (AND/OR/NOT/MAX/MIN) LIKE GROUP BY HAVING ORDER BY

Ch. 1 - SQL

SQL stands for Structured Query Language and is internationally recognized and understood by all commercial DBMS products

multiset

SQL tables cannot really be a set of tuples because there can't be identical members--instead it is a multiset wherein the select stmt can be used to choose a distinct option.

SELECT

Selects attributes from rows in one or more tables or views

A business rule is a statement that defines or constrains some aspect of the business.

TRUE

A multivalued attribute may take on more than one value for a particular entity instance

TRUE

A time stamp is a time value that is associated with a data value.

TRUE

An attribute whose values can be calculated from related attribute values is called a derived attribute.

TRUE

An entity is a person, place, object, event, or concept in the user environment about which the organization wishes to maintain data.

TRUE

An entity type name should always be a singular noun.

TRUE

In an E-R diagram, an associative entity is represented by a rounded rectangle.

TRUE

One of the roles of a database analyst is to identify and understand rules that govern data.

TRUE

While business rules are not redundant, a business rule can refer to another business rule.

TRUE

First normal form (1NF)

Table format, no repeating groups, and PK identified

Terms used by SQL for relational model terms

Table--relation Row--tuple Column--attribute

Ch. 1 - Integrated Tables

Tables that store both data and the relationships among the data

Validation Text

Text is displayed in a message box then the user is required to enter again.

Conversion to 2NF occurs only when ____

The 1NF has a composite PK.

Mapping (SELECT-FROM-WHERE block)

Simple SQL query formed by three clauses SELECT <attribute list> FROM <table list> WHERE <condition>

Data warehouse

Stores data in a format optimized for decision support

Data warehouse

Stores data in a format optimized for decision support.

Current generation DBMS software

Stores data structures, relationships between structures, and access paths, Defines, stores, and manages all access paths and components

Data dictionary

Stores definitions of the data elements and their relationships

Data dictionary

Stores definitions of the data elements and their relationships.

Analytical database

Stores historical data and business metrics used exclusively for tactical or strategic decision making, data warehouse

Data Mart

Subsets of data warehouses; used to analyze a business unit

Overlapping Subtypes

Subtypes that contain nonunique subsets of the supertype entity set; each entity instance of the supertype may appear in more than one subtype. Example: a person may be an employee, a student, or both. STUDENT AND EMPLOYEE OVERLAP.

Multiuser database

Supports multiple users at the same time, Workgroup databases, Enterprise database

1NF

-all key attributes are defined -there are no repeating groups in the table. Each row/column intersection contains one and only one value, not a set of values. -all attributes are dependent on the primary key.

performance tuning

Activities that make a database perform more efficiently in terms of storage and access speed

Special Operators

BETWEEN, IS NULL, LIKE, IN, EXISTS, DISTINCT

Database Environments

Conceptual, Logical, and physical

Record Operations

Create, Read, Update, Delete

CREATE TABLE AS

Creates a new table based on a query in the user's db schema

Data dictionary management

Data dictionary

Distributed database

Data is distributed across different sites

Centralized database

Data is located at a single site

Cloud Computing Data Architect

Design and implement the infrastructure for next generation cloud database systems, internet technologies, cloud storage technologies, data security, performance tuning, large databases, etc.

Database Architect

Design and implementation of database environments, DBMS fundamentals, data modeling, SQL, hardware knowledge, etc.

Database Designer

Design and maintain databases, system design, database design, SQL

UNIQUE

Ensures that a column will not have duplicate values

Ch.1 - What is the most popular data modeling technique?

Entity-Relationship (ER) Data Modeling

Referential Integrity

Foreign key value would refer to a primary key value of another table, or it can be null.

Wizard

Form & Report

GUI

Graphical user interface

Each vendor can supply many parts to any number of warehouses, but need not supply any parts.

In the figure shown below, which of the following business rules would apply?

A person can marry at most one person.

In the figure shown below, which of the following is true?

Structured data

It results from formatting, Structure is applied based on type of processing to be performed

DISTINCT

Limits values to unique values

Extensions

Optional modules in SQL standards help specify databases.

ORDER BY

Orders the selected rows based on one or more attributes

Data storage management

Performance tuning

Semi structured data

Processed to some extent.

Desktop database

Runs on PC

Decimal

Stores numbers from -1038 - 1 through to 1038 - 1 (.mdb)

Integer

Stores numbers from -32,768 to 32,767 (no fractions)

SQL

Structured Query Language

SQL

Structured Query Language--shows how (in what order) to execute queries.

A business rule should be internally consistent.

TRUE

information

The result of processing raw data to reveal its meaning

multi-dimensional

created with input from relative data bases

Entity

exists in itself

Data in columns

have same format

user requests info--> DBMS searches database--> Database sends info back to user

interaction btwn user, Database software and database

Composite or Compound Key

made up of more than one field

1:1

rare

Cardinality

the numeric relationship with the RD

metadata

元資料

record

記錄

Relationships depicted with specialization hierarchies are described in terms of ______

"is a" relationships. Example: a pilot IS AN employee. a mechanic IS AN employee. an accountant IS AN employee.

Third normal form (3NF)

2NF and no transitive dependencies

ad hoc query

A "spur-of-the-moment" question

File

A collection of related records

Tuple

A row in a relation.

Database communication interfaces

Accept end user requests via multiple, different network environments

Default value

Automatically place a value in a field when a new record is created.

Object oriented database

Contain complex data and complex data relationships

T/F? The highest level of normalization is always the most desirable.

False; the higher the normal form, the more relational join operations needed to produce a specified output. And more resources are needed by the DB system to respond to end user queries. So sometimes you need to denormalize.

Design

Table & Query

Functional dependence

The attribute B is fully functionally dependent on the attribute A if each value of A determines one and only one value of B. Example: PROJ_NUM --> PROJ_NAME proj num is the determinant attribute and the proj name is the dependent attribute. Review definition from previous exam.

Null

Where the entry is left empty

Date Function

a procedure that returns that current system date

self join

join table to self w/ left join or inner join employee/supervisor example

data

raw facts

information

result of processing raw data to reveal its meaning

Domain

set of possible values for a give attribute

distributed database

分佈式資料庫

analytical database

分析資料庫

Types of Databases by location

-Centralized (support data located at a single site) -Distributed (supports data distributed across several sites, difficult to administer, can lessen network delays)

Types of Databases by Size

-Single-user (supports only one user at a time) -Workgroup (multi-user that supports small group of users) -Enterprise (multi-user database that supports an entire organization)

Database Views

-User (Business Oriented) -Logical/Conceptual (Programmers, Designers, DBA) -Physical (Computer & the DBMS)

Problems with conventional file systems

-each application program uses its own files -each file is owned by the department -managing them is time-consuming -not easily shared -data redundancy

Database system environment is composed of 5 components

-hardware -software -people -procedures -data

Types of Databases by use

-transaction (production) supports a company's day-to-day operations -data warehouse (for data mining) stores data used to generate information

1 to many

1 area to many readers one side (primary key) can have unique index but many side can't (duplicates okay or no)

Flat File

1 table Records Fields

Fields

1- Name 2- Length (#of bytes) 3- Type of data

A table is in 3NF when:

1. It is in 2NF 2. It contains no transitive dependencies

Disadvantages of stored derived attributes

1. Requires constant maintenance to make sure derived value is current

Second normal form (2NF)

1NF and no partial dependencies

Norms

1NF, 2 NF, 3NF

If the 1NF has a single-attribute PK, then the table is automatically in _____.

2NF

How to write not equal to

<> or !=

Comparison Operators

=, <, >, <=, >=, <> Used in conditional expressions

Object/Relational Database Management System (O/R DBMS)

A DBMS based on the ERDM.

data dictionary

A DBMS component that stores metadata

Relational Database Management System (RDBMS)

A DBMS which hides complexity from users, who see the database as a collection of tables in which data is stored.

Problem domain

A clearly defined area within the real-world environment, with well defined scope and boundaries, that will be systematically addressed.

Pointer

A field of data indicating a target address that can be used to locate a related field or record of data

Record

A logically connected set of one or more fields that describes a person, place, or thing

Normalization

A process for evaluating and correcting table structures to minimize data redundancies, thereby reducing the likelihood of data anomalies. Works through a series of stages called normal forms.

data management

A process that focuses on data collection, storage, and retrieval

Inheritance

A property that enables an entity subtype to inherit the attributes and relationships of the supertype. All entity subtypes inherit their PK attribute from their supertype.

query

A question or task asked by an end user of a database in the form of SQL code

Data model

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

Entity Instance or Occurrence

A row in the relational table in the ER model.

Single-valued attribute

An attribute that can only have a single value. Example: a person can only have one social security number. Example: a manufactured part can only have one serial number. This is not necessarily a simple attribute!! Example: a serial number is single-valued but it is a composite attribute bc it can be subdivided into the region it was produced, plant within the region, blah blah blah. like how SSN numbers mean certain things.

Derived Attribute

An attribute whose value is calculated (derived) from other attributes. Does not need to be physically stored within the database; but can be derived instead by using an algorithm. Sometimes called computed attributes. Example: Age can be derived by using the difference between the current date and the person's DOB. Example: Total cost of an order can be derived by multiplying the quantity ordered by the unit price.

Existence-independent

An entity that can exist apart from all of its related entities. AKA Strong Entity or Regular Entity. Example: It is possible for a PART of a produce to exist independently from a VENDOR in the relationship "PART is supplied by VENDOR"

Join index

An index on columns from two or more tables that come from the same domain of values

Determinant

Any attribute whose value determines other values within a row.

Multivalued Attributes

Attributes that can have many values. Example: a person may have several college degrees. Or a car's color can be divided into many colors for the roof, body, and trim. So CAR_VIN would be the PK but CAR_COLOR would be a multivalued attribute of the CAR entity.

DBMS

Collection of programs that creates and manages the database and controls access

Database management system (DBMS)

Collection of programs, Manages the database structure, Controls access to data stored in the database

Discipline specific databases

Contains data focused on specific subject areas

Discipline-specific database

Contains data focused on specific subject areas.

software

DBMS

database software

DBMS// creates database add, change, delete data

Ch. 1 - Metadata

Data about data

Metadata

Data about data, which the end user data are integrated and managed, Describe data characteristics and relationships

Metadata

Data about data, which the end-user data are integrated and managed.

metadata

Data about data; that is, data about data characteristics and relationships

Data dependence

Data access changes when data storage characteristics change

Sorting

Data is arranged in some kind of order

Data anomaly

Develops when not all of the required changes in the redundant data are made successfully

Data anomaly

Develops when not all of the required changes in the redundant data are made successfully.

American National Standards Institute (ANSI)

Devised a framework for data modeling based on 3 primary levels of data abstraction: external, conceptual, and internal.

Required option

Dictates whether a null value is allowed

Data inconsistency

Different versions of the same data appear in different places

DBMS benefits

Eliminates most of the file system's problems. Stores data structures, relationships, and access paths. Defines all components.

Online analytical processing (OLAP)

Enable retrieving, processing, and modeling data from the data warehouse

Backup and recovery management

Enables recovery of the database after a failure

Online analytical processing OLAP

Enables retrieving, processing, and modeling data from the data warehouse.

Data Definition Language (DDL)

Enables the DBA to define the schema components.

Entity Integrity

Every table must have its own primary key and that each has to be unique and not null

Lack of Design and Data Modeling Skills

Evident despite the availability of multiple personal productivity tools being available, vital in the data design process, decreases communication between the designer, user, and the developer

Many-to-Many (M:N or *..*)

Ex. An employee may learn many job skills, and each skill can be learned by many employees.

T/F? You should implement M:N relationships and multivalued attributes in the RDBMS.

FALSE. Each column and row intersection represent a single data value. So if multivalued attributes exist, the designer has to decide on splitting the multivalued attribute into several new attributes (one for each component of the multivalued attribute) OR create a new entity composed of the multivalued attribute's components.

Single

Floating point decimal numbers from -3.4 x 1038 to 3.4 x 1038 with 6 digits of precision.

Database Design

Focuses on the design of the database structure that will be used to store and manage end user data, Well designed database, Poorly designed database causes difficult to trace errors

Data Management

Generation, storage, and retrieval of data

Data management

Generation, storage, and retrieval of data.

Replication ID

Globally unique identifies (GUID)

Entity-Relational Model (ERM)

Graphically depicts entities and relationships in a database structure. Normally represented in an entity-relationship diagram (ERD).

Database Security Officer

Implement security policies for data administration, DBMS fundamentals, database administration, SQL, data security technologies, etc.

FALSE

In the figure below, one might want to create a single-attribute surrogate identifier to substitute for the composite identifier.

Each employee can supervise one to many employees

In the following diagram, which answer is true?

SQL environment

Installation of an SQL compliant RDBMS on a computer system

Long Integer

Integer values from - 2147483648 to 2147483648

Role of the DBMS

Intermediary between the user and the database, Enables data to be shared, Presents the end user with an integrated view of the data, Receives and translates application requests into operations required to fulfill the requests, Hides database's internal complexity from the application programs and users

GO OVER PROJECT 1 AND ASSIGNMENT 3 ON HOW TO WRITE SQL STATEMENTS. PRACTICE A LITTLE WITH SSMS!

Know some data types and stuff that you did for project 1. Look at page 271 in textbook pdf and read the bottom for the differences in data types.

MAKE SURE TO REVIEW INSERT, UPDATE, DELETE syntax

LOOK OVER HIS PPT SLIDES. PRACTICE WRITING THESE STATEMENTS.

Query language

Lets the user specify what must be done without specifying how.

Data integrity management

Minimizes redundancy and maximizes consistency

ALTER TABLE

Modifies a table's definition (adds, modifies, or deletes attributes or constraints)

UPDATE

Modifies an attribute's values in one or more table's rows

DBMS traits

Multiuser access control, backup and recovery management, and integrity management.

Data transformation and presentation

Transforms entered data to conform to required data structures

Unstructured data

Type of database that exists in its original state.

Ch. 1 - What are the components of a database system?

Users, database applications, dbms, database

CHECK

Validates data in an attribute

3 V's of Big Data

Volume, Variety, Velocity

social media

Web and mobile technologies that enable "anywhere, anytime, always on" human interactions

Logical independence

When a change in the internal model does not affect the conceptual model.

Object-Oriented Data Model (OODM)

Where data and relationships are contained in a single structure known as an object. Is the basis for the object-oriented database management system. Said to be a semantic (meaningful) data model.

GROUP BY clause

Within the SELECT statement; it is used to group rows into smaller collections. Aggregate functions then summarize the data within each smaller collection. Generally used when you have attribute columns combined with aggregate functions in the SELECT statement.

Data warehouse

a collection of databases

data quality

a comprehensive approach to promoting the accuracy, validity, and timeliness of the data

Many-to-Many Relationship

a row in table A can have many matches in table B and vice versa

data warehouse

a specialized database that stores data in a format optimized for decision support; contains historical data obtained from the operational databases as well as data from external sources

AutoNumber

a unique sequential number (incremented by 1) or a random number assigned to a new record in a table

performance tuning

activities that make a database perform more efficiently in terms of storage and access speed

instance

actual content of database at some point in time

database processing approach

all data integrated, no duplicates definition of data separate from app entire org. owns database

range control

allowable value limitations (constraints or validation rules)

domain constraints

allowable values for an attribute

Module

an object in Relational Database Management Systems that you type code in, specifically Visual Basic

A ________ specifies the number of instances of one entity that can be associated with each instance of another entity.

cardinality constraint

Classification

categorizing; looks for patterns in shopping behavior to target marketing efforts

Attributes

characteristics (fields) of entities

attribute

column

join condition

combines two tuples EX. WHERE Dname = 'Research' AND Dnumber = Dno;

metadata

data about data, through which the end-user data are integrated and managed

Data modeling may be the most important part of the systems development process because:

data characteristics are important in the design of programs and other systems components.

DML

data manipulation language

online analytical processing OLAP

decision support system (DSS) tools that use multidimensional data analysis techniques; creates an advanced data analysis environment that supports decision making, business modeling, and operations research. [Title and acronym.]

object database model

define own data types

Data admin

determines and monitors the employees' access to the DBMS

data management

discipline that focuses on the proper generation, storage, and retrieval of data

Optional Attribute

does not require a value, may be left empty

file-based systems approach

each program defines and manages own data info in separate files data could duplicate solution: writing codes so that 1 student can only access her info ex. NDSU no database= pay tuition THEN register

Customers, cars, and parts are examples of

entities

Associations

establishes the relationship between two entities or events

Total completeness

every supertype occurrence must be a member of at least one subtype.

Weak Entity

existence dependent has a primary key that is partially or wholly derived from parent entity in the relationship

data inconsistency

exists when different versions of the same data appear in different places

database abstraction

hiding of details of data storage that are not needed by most users; aim to separate user's views of database from how it is physically represented

data

integrated/shared: diff. people access same

ad hoc query

is a spur-of-the-moment question

Practical significance of data dependence

is difference between logical and physical format

outer join

join 2+ query tables for 1 result includes non matching rows all records from one table and only records that have matching fields in other

schema

logical structure of database

production database

main database designed to keep track of the day-to-day operations of a company; also known as transactional databases

NOT

negates the result of a conditional expression DUH... gives you the opposite

entity integrity constraints (primary key constraints)

no duplicates or null values allowed for primary keys; set automatically when primary key chosen

Data Integrity

overall completeness, accuracy and consistency of data, meaning data is intact and unchanged

Schema

planned design structure of a database

Composite Identifier

primary key composed of more than one attribute

Foreign Key

primary key of one table appearing in another table

Weak Relationship

primary key of related entity does not contain a primary key component of the parent entity

Normalization

process of organizing a database to reduce redundancy & improve data integrity

normalization

process that generally results in a well-structured relation

Data integrity

process to ensure data is not corrupted or lost during data entry or extraction and transformation

Query language

provides instructions and procedures to retrieve data from database

Domain

range of values for a field

end user data

raw facts of interest to the end user

Clickstream

records where a user clicks on a web page

Normalization

reduces data complexity and eliminates data redundancy

tuple

row

qualify

same name can be used to two or more attributes as long as the attributes are in different relations--so it is qualified with the relation name to prevent confusion EMPLOYEE.Name

record

set of fields with all info each has same fields in same sequence ex. person's info

database

shared, integrated computer structure that stores a collection of end-user data and metadata; a collection of self-describing data; resembles a very well-organized electronic filing cabinet in which powerful software (the dbms) helps manage the cabinet's contents

Data cleansing

should be performed after a data audit on tables that are merged or imported

referential integrity constraints

specified between two relations and require foreign key; ensures that the database doesn't contain any unmatched foreign keys; default constraints usually chosen but can be changed

selection condition

specified by the WHERE clause and is the Boolean condition that must be true for any retrieved tuple. Causes for the selection of a particular tuple of interest.

Primary key

specifies one or more attributes that make up the primary key of a relation. If only one attribute te clause can follow the attribute directly--EX: Dnumber INT PRIMARY KEY;

Completeness Constraint

specifies whether each entity supertype occurrence must also be a member of at least one subtype. Can be partial or total.

disadvantages of DBMS

strict schema & multiple tables/ relations complexity size cost of DBMS, hardware & conversion higher impact of failure

The common types of entities are:

strong entities, weak entities, and associative entities(all of the above)

multiuser database

supports multiple users at the same time

online transaction processing database

supports online transaction processing for day-to-day operations of an organization

single user database

supports only one user at a time

A fact is an association between two or more:

terms

A simultaneous relationship among the instances of three entity types is called a(n) ________ relationship.

ternary

Short Text

text or combinations of text and numbers that have a max size of 255 characters

data types

text, numeric, currency, date...

Surrogate

the DBMS chooses the PK

determinants

the attributes on the left side of the arrow when converting to 2nf

projection attributes

the attributes whose values are to be retrieved.

One-to-Many Relationship

the most common type of relationship. A row from table A can have many matches in table B, but the row in table B can only have one match in table A

1NF to 2NF means to remove _____

the partial dependencies

One-to-One Relationship

the row in table A can only have one match in table B and vice versa

Domain

the set of possible values for a given attribute. Example: GPA has a domain (0,4) because the lowest possible GPA is 0 and the highest is 4. Example: Gender only has 2 possibilities (M or F)

Hierarchical database

tree-like structure; parent child relationships

In an E-R diagram, there are ________ business rule(s) for every relationship.

two

A relationship between the instances of a single entity type is called a(n) ________ relationship

unary

Three types of relationship degrees

unary (one entity) binary (two entities) ternary (three entities)

data dictionary

used by the dbms to look up the required data component structures and relationship

Data Control

used to control access to data stored in a database (Authorization)

OLAP

used to generate reports that have info or business intelligence

Foreign key

used to uniquely relate a record to another table where that field is the primary key

Disadvantages of not storing derived attributes

uses CPU processing cycles increases data access time adds coding complexity to queries

An entity type whose existence depends on another entity type is called a ________ entity.

weak

social media

web and mobile technologies that enable "anywhere, anytime, always on" human interaction

enterprise database

when the database is used by the entire organization and supports many users (more than 50, usually hundreds) across many departments

file access

where physical records are accessed

file organization

where records are physically located on a disk

A good data definition will describe all of the characteristics of a data object EXCEPT:

who can delete the data

reject

won't update due to the RESTRICT operation.

social media

社交媒體

T/F? Entity and object are interchangeable and represent real-world objects.

True

Structured data

Type of database resulting from formatting. Based on type of processing to be performed.

Data Redundancy

Unnecessarily storing same data at different places, Islands of information

Captions

Used to abbreviate field names the purpose of using captions for Field names in the Data Dictionary

Ch. 1 - Primary Key

Used to create the relationships between the tables

Data Manipulation Language (DML)

Used to define the environment in which the data can be managed and is used to work with data in the database.

Connectivity

Used to describe the relationship classification. Example: 1:M

Input Mask

Used to input data if the values to be entered in a field have exactly the same format. E.g the beginning of a phone number or the end of an email (GMail)

ORDER BY

Useful when the listing order is important to you. You put ORDER BY columnlist ASC/DESC

Physical independence

When a change in the physical model does not affect the internal model.

Can attributes share a domain?

YES. Example: a student address and professor address have the same domain of all possible addresses.

Yes/No

Yes and No values and fields that contain only one of two values (Yes/No, True/False, On/Off, 0/1, etc.)

How to put many to many relationships in tables?

You make 3 tables and the third one is a junction table that has the PKs of the other two ENTITY tables. Then you link that PK of ENTITY tables to the junction table.

Function

a Visual Basic procedure that performs a task or calculation and returns a result

candidate key

a collection of attributes that satisfies the requirements for a primary key

database management system

a collection of programs that manages the database structure and controls access to the data stored in the database; intermediary between the user and the database

Form

a database GUI

operational database

a database designed primarily to support a company's day-to-day operations; also known as a transactional database or production database

Table

a database object that is organized by rows and columns

XML database

a database system that stores and manages semistructured XML data

discipline specific database

a database that contains data focused on specific subject areas

single user database

a database that supports only one user at a time

Candidate Key

a field that could have served as key field but isn't

Attachment

a file that can be attached per record with a size limit of 2 GB

referential integrity

a foreign key value (in the relation on the many side) MUST match a primary key value in the relation on the one side or be null

third normal form

a form where no transitive dependencies exist

transitive dependency

a functional dependency between two or more non key attributes

Record

a group of fields

physical record

a group of fields stored in adjacent memory locations and retrieved together as a unit

non loss decomposition

a join operation should restore the original information

Common field when doing joins is

a key field

Extensible Markup Language

a metalanguage used to represent and manipulate data elements. Permits the manipulation of a document's data elements.

workgroup database

a multiuser database supporting a relatively small number of users (less than 50) or a department within an organization

file

a named collection of related records

query language

a nonprocedural language that is used by a DBMS to manipulate its data

second normal form

a normal form where there are no partial dependencies

Structured Query Language

a powerful and flexible relational database language composed of commands that enable users to create database and table structures, perform various types of data manipulation and data administration, and query the database to extract useful information

Composite Identifier

a primary key composed of more than one attribute.

composite key

a primary key that consists of more than one attribute

IIF Function

a procedure that returns a specified value based on a true or false result. Abbreviated for "Immediate If"

Format Function

a procedure that returns a string containing an expression formatted according to instructions contained in a format expression

Time Function

a procedure that returns that current system time

DateDiff Function

a procedure that returns the number of time intervals between two specified dates

select-project-join

a query that only involves selection and join conditions plus projection attributes. EX: SELECT Pnumber, Dnum, Lname, Address, Bdate FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND Plocation = 'Stafford';

first normal form

a relation is in this form if all multivalued attributes have been removed

Defining Relationships

a relationship works by matching data in key columns to other columns in a different table, usually with the same name. In most cases, the relationship matches the primary key from one table with a foreign key in the other table.

desktop database

a single-user database that runs on a personal computer

query

a specific request issued to the DBMS for data manipulation; simply put it is a question

Lookup Wizard

a wizard that helps you define either a simple or complex lookup field

null value control

allowing or prohibiting empty fields

pattern matching

allows for comparison conditions based on only parts of a character string.

Data mining

also called analytics; analyzes data in databases to find new patterns or trends

referential triggered action

alternative action that can be attached to a foreign key if update operation is illegal.

field

an alphabetic or numeric character or group of characters that defines a characteristic of a person, place, or thing

Alias

an alternate name given to a column or table in any SQL statement. *know how to write an alias*

primary key

an attribute (or combo of attributes) that uniquely identifies each row in a relation

foreign keys

an attribute (or set of attributes) within one table that matches or "links to" the candidate key of some other table allow data in tables to be linked and cross referenced by matching the same data values in both tables matching must take place to primary or candidate keys

Composite Attribute

an attribute that can be further subdivided to yield additional attributes. Example: Address can be subdivided into street, city, state, zip code. DON'T CONFUSE WITH COMPOSITE KEY. It's wise to change these into a series of simple attributes.

A mutually exclusive relationship is one in which:

an entity instance can participate in only one of several alternative relationships.

The ERM refers to a table row as ____

an entity instance or entity occurrence At the ER modeling level, an entity refers to the entity set; not the single entity instance

Calculated

an expression that uses data from one or more fields. You can designate different result data types from the expression

Report

an object in a database designed for formatting, calculating, printing, and summarizing data

OLE Objects

an object that can store pictures, audio, video, or other BLOBs (Binary Language Objects)

Query

an object that performs create, read, update, and delete operations on the database

database system

an organization of components that defines and regulates the collection, storage, management, and use of data in a database environment

query result set

answer to a query sent back by the DBMS to the application

users

application programmers, end users

An entity type name should be all of the following EXCEPT

as short as possible

Relationships

associations between entities that always operate in both directions

derived attribute

attribute whose value can be determined from another attribute (ex: can derive age from birthdate)

Derived Attribute

attribute whose value is calculated from other attributes (derived using an algorithm)

A person's name, birthday, and social security number are all examples of

attributes

Multivalued Attribute

attributes that have many values

Single-valued Attribute

attributes that have only a single value

candidate keys

can be multiple; all the candidates for the primary key (though only one primary key is chosen) ensures that each tuple in a relation is unique can be used to identify a tuple in the relation

CHECK clause

can restrict attribute or domain values. Example: department numbers are restricted between 1 and 20

M:N or M:M

can't be implemented in relational model can be changed into two 1:M relationships

Data independence

changes in the data storage characteristics without affecting the application programs

Cast (coerce)

changing string comparisons into equivalent strings--like TIME to TIMESTAMP.

DBMS (Database Management System)

collection of programs that facilitates the process of defining, constructing, and manipulating databases for various applications

file

collection of related records ex. includes group of people

relational data model

collection of relations (tables), each containing tuples (rows) and attributes (columns)

Database

collection of structured information that is designed to manage large amounts of information

data hardware software users

components of database system

An attribute that can be broken down into smaller parts is called a(n) ________ attribute

composite

An attribute that uniquely identifies an entity and consists of a composite attribute is called a(n):

composite identifier

data quality

comprehensive approach to promoting the accuracy, validity, and timeliness of the data

Data governance

concerned with the mgmt of data, including quality through effective utilization, availability, and protection

general purpose database

contain a wide variety of data used in multiple disciplines

discipline specific database

contain data focused on specific subject areas

Data warehouse

contains current and historical data from variety of sources; enables analytics of operational data

Database Admin

creation and management of entire database mgmt system

DDL

data definition language; standardized language to define the schema of a database; tasks include creating, modifying & removing database objects

unstructured data

data that exist in their original, raw state; that is, in the format in which they were collected

semistructured data

data that have already been processed to some extent

domain constraint

data types and formats must match those specified; set automatically when data type chosen

Data Manipulation Language

database language used for selecting, inserting, updating, and deleting data in a database

cloud database

database that is created and maintained using cloud data services

distributed database

database that supports data distributed across several different sites

centralized database

database that supports data located at a single site

DEFAULT<value>

defined as the value if no value is given. If not included the default becomes NULL as long as the attribute does not have a NOT NULL constraint.

A business rule:

defines or constrains some aspect of the business. asserts business structure. controls or influences the behavior of the business. (all of the above)

what is the DBMS capable of?

defining database (DDL) manipulate database (SQL) control redundancy restrict unauthorized access enforce integrity constraints provide multiple user interfaces/ veiws provide concurrent access provide mechanism for recovery provide backup allows representation of complex relationships between data

relational database schema, S

definition of a set of relations that are to be stored in the database S = {R1, R2, .... , Rn} where each Rn is a table

null

denotes absence of a value; is a member of every domain

An attribute that can be calculated from related attribute values is called a(n) ________ attribute.

derived

The total quiz points for a student for an entire semester is a(n) ________ attribute

derived

Repeating Group

derives its name from the fact that a group of multiple entries of the same type can exist for any single key attribute occurrence. Example: each time a new record is entered for a project, the number of entries in the group grows by one. This is a repeated group! A relational table must not contain any of these.

business intelligence

describes a comprehensive approach to capture and process business data with the purpose of generating information to support business decision making

Connectivity

describes the relationship classification

Optional Participation

does not require corresponding entity occurrence in a particular relationship

If speed is an issue

don't use indexed sequential

what are some potential problems with the file system approach?

duplicated data in different files and locations difficulty in accessing data programmer effort for any new tasks data isolation- due to multiple files and formats integrity problems- part of code update inconsistencies- failures may leave data in an inconsistent state concurrent access- may not be supported

file system approach

each user stores the information they require to do their job in their own files on their own machines/ servers & writes own programs to access data; files are separate and not cross-indexed; each program "owns" and accesses its own files

Participants

entities that participate in a relationship

Existence Independence

entity exists apart from all its related entities

Existence Dependence

entity exists in the database only when it is associated with another related entity occurrence

The logical representation of an organization's data is called a(n):

entity-relationship model.

foreign key

field in table that matches primary key of different table

Hierarchy of Data

field, record, file, data types

Columns

fields/attributes

analytical database

focuses primarily on storing historical data and business metrics used exclusively for tactical or strategic decision making

data processing specialist

hired to create computer-based system.

use join

how to query multiple tables

A(n) ________ is the relationship between a weak entity type and its owner.

identifying relationship

cascade deletion

if a record in the parent table is deleted automatically delete related rows in the child table

Normalization helps ____

improve the existing data structure and create an appropriate database design. Whether designing a new database structure or modifying an existing one, the normalization process is the same.

Business intelligence

includes data mining, predictive analytics, and OLAP

Relationship Degree

indicates the number of entities or participants associated with relationship

inner join

join 2+ query tables for 1 result this way would not include employee's without phone number records selected from 2 tables only when records have same value in common that links them

Long Text

lengthy text or combinations of text and numbers that have a max size of 63,999 characters

Business policies and rules govern all of the following EXCEPT

managing employees

A relationship where the minimum and maximum cardinality are both one is a(n) ________ relationship.

mandatory one

many to many

many products to many consumers

structural dependence

means that access to a file is dependent on its structure

well-structured relations

minimizes redundancy (it does not eliminate it entirely). Also allows for insertions, deletions, and modifications without compromising the integrity of the data

relational database model

most common stores data in tables with rows and columns each row primary key and each column unique name

Required Attribute

must have a value, cannot be left empty

relation

named, two dimensional table of data, consisting of a set of named columns (attributes) and an arbitrary number of unnamed rows

1:M

normal

Partial completeness

not every supertype occurrence is a member of a subtype; some supertype occurences may not be members of any subtype.

file processing

often use when writing a program and open in text file to work with data

primary key

one attribute per relation where 1. there can only be one such primary key per table 2. the primary key can never contain the null value 3. all values entered for the primary key must be unique (in writing we underline primary key)

Identifier

one or more attributes that uniquely identify each entity instance

tuple

one record or data set in a row

1 to 1

one student to one ID both primary and foreign keys have unique index (Yes no duplicates)

Example of Composite Entity

prescription entity to connect doctor, patient, and drug entities

entity integrity

primary key components cannot be null

Rows

records

database design

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

rename

rename relation attributes with the query in SQL by giving them aliases.

An attribute of an entity that must have a value for each entity instance is a(n):

required attribute

An attribute that must have a value for every entity (or relationship) instance is a(n):

required attribute

Disadvantages of of storing derived attributes

requires constant maintenance to ensure derived value is current

Mandatory Participation

requires corresponding entity occurrence in a particular relationship

Advantages of storing derived attributes

saves CPU processing cycles saves data access time data value readily available can be used to keep track of historical data

Advantages of not storing derived attributes

saves space computation always yields current value

Database

shared, integrated computer structure that stores: -End user data -Metadata (data about data) -Triggers (event that causes procedure to occur) -Procedures (sorting) -Indexes

database approach

single repository of data (which may be distributed) is maintained that is defined once and then accessed by various users via a DBMS

flat file database

single table database

field

smallest meaningful data unit group of 1+ characters with specific meaning ex. last name in table

Data storage management

stores data and related data entry forms, report definitions

relation

table

data integrity

the condition in which all of the data in the database are consistent with the real-world events and conditions

online transaction processing

the systems that support a company's day-to-day operations. Databases that support this are known as OLTP databases, transactional databases, or operational databases

2NF to 3NF means to remove ____

the transitive dependencies

physical data format

the way a computer "sees" (stores) data

logical data format

the way a person views data

A value that indicates the date or time of a data value is called a(n):

time stamp.

Macro

tool that allows automation of tasks and add functionality to your forms, reports, and controls

denormalization

transforming normalized relations into non normalized physical records specifications

primary key

uniquely identify each record in table (user ID)

structured data

unstructured data that have been formatted to facilitate storage, use, and information.

Composite Entity

used to represent a many to many relationship between two or more entities is in a one to many relationship with the parent entities composed of the primary key attributes of each parent entity may contain additional attributes that play no role in connective process

foreign key

used to represent relationships between two relations

null valued attributes

values of some attribute within a particular tuple may not apply to a particular tuple - null is used

nullify deletes

when a record is deleted in the parent table, find the related records in the child table, and set the foreign key in each of the related records to null

Recursive Query

when a table must be joined to itself.

data dependence

when change to any of the file's data storage characteristics change all data access programs

islands of information

when the structure supports scattering the data into several locations, each location is an island; often causes data to be in several different versions because the locations aren't updated consistently

referential integrity

when two tables share relationship based on data stored in tables, relationship must remain consistent any foreign key must agree with primary key referenced

data independence

when you can change the data storage characteristics without affecting the program's ability to access the data

Right Outer Join

yields all rows in Agent including those that don't have matching values in the Customer table

Left Outer Join

yields all rows in customer table, including those that don't have a matching value in agent

transactional database

事務資料庫

enterprise database

企業資料庫

semistructured data

半結構化資料

ad hoc query

即席查詢

Extensible Markup Language (XML)

可擴展標記語言(XML)

business intelligence

商業智能

single-user database

單用戶資料庫

multiuser database NoSQL

多用戶資料庫NoSQL

discipline-specific database

學科特定資料庫

workgroup database

工作組資料庫

performance tuning

性能調校

operational database

操作型資料庫

file

文件

query

查詢

query result set

查詢結果集

query language

查詢語言

desktop database

桌面資料庫

field

欄位

physical data format

物理資料格式

production database

生產資料庫

knowledge

知識

online analytical processing (OLAP)

線上分析處理(OLAP)

data dependence

資料依賴

data warehouse

資料倉儲

data redundancy

資料冗餘

data dictionary

資料字典

database

資料庫

database management system (DBMS)

資料庫管理系統(DBMS)

database system

資料庫系統

database design

資料庫設計

data independence

資料獨立性

data anomaly

資料異常

data integrity

資料的完整性

data management

資料管理

data processing (DP) specialist

資料處理(DP)專家

data quality

資料質量

information

資訊

islands of information

資訊島

general-purpose database

通用資料庫

logical data format

邏輯資料格式

centralized database

集中式資料庫

cloud database

雲資料庫

unstructured data

非結構化資料

grade/degree

# of attributes (constant)

cardinality

# of tuples (variable)

File Types re:data

-master -transaction -reference/look-up -temporary -print -archive

Keys

-one or more fields -record must have unique value

Kinds

-relational (data) -object-oriented -no-SQL -Data warehouses

INSERT Command

1) can be used to add a single tuple to a relation 2) allows user to specify explicit attribute names that correspond to the values provided in INSERT command. 3) INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn) VALUES ('Richard,' "Marini,' '4,' '6543234455')

Attributes of SQL schema

1) has schema name 2) an authorization identifier--to ID owner of that schema 3) descriptors--for each element in schema 4) elements--like tables, constraints, views, domains, and other constructs

List of data types in SQL

1) numeric--like into 2) character-string-can be fixed length like CHAR(n) or varying length CHAR VARYING(n) 3) bit-string--can be fixed or varying too. BIT(n) or BIT VARYING (n) 4) Boolean--values of True and False 5) DATE--has ten positions n the form YYYY-MM-DD 6) Timestamp--includes DATE and TIME fields 7) interval--related to DATE, TIME, and TIMESTAMP in the data type. It is a relative value that can be used to increment or decrement.

DELETE Command

1)removes tuples in a relation 2) includes a WHERE clause similar to that in the SQL query to select tuples to be deleted 3) tuples are only deleted in one table at a time 4) Can propagate to tuples in other relations via a referential triggered action. 5) DELETE FROM EMPLOYEE WHERE Lname = 'Brown';

UPDATE Command

1)used to modify attribute values of one or more selected tuples 2)WHERE clause selects tuples to be modified. 3)Here the primary key value may propagate to foreign key values via referential triggered acton is specified. 4) UPDATE PROJECT SET Plocation = 'Bellarie,' Dnum=5 WHERE Pnumber = 10;

Two categories of SQL functions:

1. Data definition language (DDL)- includes commands to create db objects like tables, indexes, and views. 2. Data manipulation language (DML)- includes commands to insert, update, delete, and retrieve data within the db tables.

Weak Entity

1. Entity is existence-dependent; it cannot exist without the entity with which it has a relationship. 2. Entity has a PK that is partially or totally derived from the parent entity in the relationship.

What are the first three stages of Normalization?

1. First normal form (1NF) 2. Second normal form (2NF) 3. Third normal form (3NF) 2NF is better than 1NF, and 3NF is better than 2NF. 3NF is as high as you need to go for normalization but there is 4NF.

1NF to 2NF steps:

1. Make new tables to eliminate partial dependencies (for each component of the PK that acts as a determinant in a partial dependency, create a new table with a copy of that component as the PK. Keep the determinants in the original table too bc they will be FKs for the relationship to relate the new tables to the original table). 2. Reassign corresponding dependent attributes

2NF to 3NF:

1. Make new tables to eliminate transitive dependencies (for every transitive dependency write a copy of its determinant as a PK for a new table. If you have 3 different transitive dependencies, you will have 3 different determinants. Keep the determinant in the original table to serve as FK) 2. Reassign corresponding dependent attributes READ PG 230 of TEXTBOOK PDF

Advantages of stored derived attributes

1. Saves CPU processing cycles 2. Saves data access time 3. Data value is readily available 4. Can be used to keep track of historical data

Advantages of not stored derived attributes

1. Saves storage space 2. Computation always yields current value

2 criteria to help determine when to use subtypes and supertypes:

1. There must be different, identifiable kinds or types of the entity in the user's environment 2. The different kinds or types of instances should each have one more attributes that are unique to that kind or type of instance.

Disadvantages of not stored derived attributes

1. Uses CPU processing cycles 2. Increases data access time 3. Adds coding complexity to queries

A specialization hierarchy provides the means to:

1. support attribute inheritance 2. define a special supertype attribute (subtype discriminator) 3. define disjoin/overlapping constraints and complete/partial constraints.

The Network Model

1969; like Heirarchical but records can have M:N relationships.

The Relational Model

1970, June; E.F. Codd of IBM publishes "A Relational Model of Data for Large Shared Databanks" in which he presents the model, based on the mathematical concept of a relation.

Entity Cluster

A "virtual" entity type used to represent multiple entities and relationships in the ERD. Formed by combining multiple interrelated entities into a single, abstract entity object. It's not actually an entity in the final ERD. Just a temporary entity used to represent multiple enttiies/relationships with the purpose of simplifying the ERD and enhancing its readability.

Surrogate Key

A PK created by the DB designer to simplify the identification of entity instances. Has no meaning in the user's environment- it exists only to distinguish one entity from another (like PKs). It has no intrinsic meaning, values for it can be generated by the DBMS to ensure that unique values are always provided. Example: auto-incrementing ID numbers

Ch. 1 - What is a data model?

A blueprint that's used as a design aid on the way to a database design

Business Rule

A brief, precise, and unambiguous description of a policy, procedure, or principle within an organization. Used to define entities, relationships, attributes, and constraints.

On what premises are business rules based?

A business rules approach is based on the following: 1. Because business rules are an expression of business policy, they are a core concept in an enterprise. 2. Natural language for end-users and a data model for developers can be used to state business rules.

Field

A character or group of characters that have a specific meaning, used to define and store data

Attribute

A characteristic of an entity.

File

A collection of related records. For example, may contain data about students at a university.

Ch. 1 - Database

A collection of related tables and other structures

data quality

A comprehensive approach to ensuring the accuracy, validity, and timeliness of data

data independence

A condition in which data access is unaffected by changes in the physical data storage characteristics

data inconsistency

A condition in which different versions of the same data yield different results

Extent

A contiguous section of disk storage space

data anomaly

A data abnormality in which inconsistent changes have been made to a database

structural dependence

A data characteristic in which a change in the database schema affects data access, thus requiring changes in all access programs

structural independence

A data characteristic in which changes in the database schema do not affect data access

data dependence

A data condition in which data representation and manipulation are dependent upon the physical data storage characteristics

operational database

A database designed primarily to support a company's day-to-day operations

analytical database

A database focused primarily on storing historical data and business metrics used tor tactical or strategic decision making

Ch. 1 - DBMS

A database management system - program used to create, process, and administer the database

XML database

A database system that manages semistructured XML data

general purpose database

A database that contains a wide variety of data used in multiple disciplines

discipline specific database

A database that contains data focused on specific subject areas

cloud database

A database that is created and maintained using cloud services, such as Microsoft Azure or Amazon AWS

multiuser database

A database that supports multiple concurrent users

single user database

A database that supports only one user at a time

Data type

A detailed coding scheme recognized by system software, such as DBMS, for representing organizational data

Secondary Key

A field that contains useful items of data often used in searches. Not always unique.

Single Key

A field where where each item of data is unique. Care must be taken when choosing a single key as some fields (family names) are not always unique.

Hash index table

A file organization that uses hashing to map a key into a location in an index, where there is a pointer to the actual data record matching the hash key

Entity Supertype

A generic entity type that is related to one or more entity subtypes. Contains common characteristics/attributes for all of its subtypes. Example: EMPLOYEE (it's a super type that has common characteristics for its entities but it has subtypes for specific kinds of employees that branch out)

Field

A group of characters, alphabetic or numeric, that has a specific meaning. Defines and stores data.

Hyperlink

A link to another location or file, typically activated by clicking on a highlighted word or image on the screen

Schema

A logical group of database objects (like tables and indexes) that are related to each other. Usually belongs to a single user or application. Useful in that they group tables by owner (or function) and enforce a first level of security by allowing each user to see only the tables that belong to that user.

centralized database

A logically related database that is stored in two or more physically independent sites

extensible markup language

A metalanguage used to represent and manipulate data elements

Ch. 2 - Null Value

A missing data value

workgroup database

A multiuser database that usually supports fewer than 50 users or is used for a specific department in an organization

SQL catalog

A named collection of schemas in a named SQL environment

Tablespace

A named logical storage unit in which data from one or more database tables, views, or other database objects may be stored

Physical file

A named portion of secondary memory such as a hard disk allocated for the purpose of storing physical records

NoSQL

A new generation of database management systems that is not based on the traditional relational database model

query language

A nonprocedural language that is used by a DBMS to manipulate its data

Entity

A person, place, thing, or event about which data will be collected and stored.

structured query language

A powerful and flexible relational database language composed of commands that enable users to create database and table structures, perform various types of data manipulation and data administration, and query the database to extract useful information

Relational Diagram

A representation of the relational database's entities, attributes within entities, and relationships between entities.

Constraint

A restriction placed on the data. Ex. A student's GPA must be between 0.00 and 4.00. A class may only have one teacher.

Hashing algorithm

A routine that converts a primary key value into a relative record number or relative file address

Ch. 1 - Database Application

A set of one ore more computer programs that serves as an intermediary between the user and the DBMS. Can read and modify SQL statements. Can present data to users in the form of forms & reports.

business intelligence

A set of tools and processes used to capture, collect, integrate, store, and analyze data to support business decision making

online analytical processing

A set of tools that provide advanced data analysis for retrieving, processing, and modeling data from the data warehouse

database

A shared, integrated computer structure that houses a collection of related data

What is the difference between a simple attribute and a composite attribute?

A simple attribute cannot be broken down into smaller components whereas a composite attribute can be. An example of a simple attribute is last name. An example of a composite attribute is mailing_address, which would have street, city, state and zip code as components.

Primary Key

A single key that must have a value.

desktop database

A single-user database that runs on a personal computer

data warehouse

A specialized database that stores historical and aggregated data in a format optimized for decision support

External Schema

A specific representation of an external view.

Internal schema

A specific representation of an internal model.

Hashed file organization

A storage system in which the address for each record is determined using a hashing algorithm

Data Dictionary

A table providing a comprehensive description of each field in the database. This commonly includes: field name, data type, data format, field size, description and example.

File organization

A technique for physically arranging the records of a file on secondary storage devices

Raw facts

A telephone number, a birth date, a customer name, and a year to date (YTD) sales value

Weak Relationship

AKA Non-identifying relationship Exists if the primary key of the related entity does not contain a primary key component of the parent entity.

Extended Entity Relationship Model (EERM)

AKA enhanced entity relationship model. The result of adding more semantic constructs to the original ER model. Uses a EERD (EER Diagram). Has entity supertypes, subtypes, and clustering.

Strong Relationship

AKA identifying relationship. Exists when the primary key of the related entity contains a PK component of the parent entity.

tuple variable

AKA iterator--loops over each individual tuple in EMPLOYEE and evaluates it based on the WHERE clause--if it satisfies the conditions, it is selected.

Disjoint Subtypes

AKA nonoverlapping subtypes; Subtypes that contain a unique subset of the supertype entity set.; each entity instance of the supertype can appear in only one of the subtypes. Example: Employee (supertype) who is a pilot (subtype) can appear only in the PILOT subtype not in any other subtypes. Indicated by the letter d.

Structural dependence

Access to a file is dependent on its own structure, All file system programs are modified to conform to a new file structure

Structural dependence

Access to a file is dependent on its own structure. All file system programs are modified to conform to a new file structure.

Manual file systems

Accomplished through a system of file folders and filing.

Extended Relational Data Model (ERDM)

Adds many OO model features to the relational database structure.

Which of the following conditions should exist if an associative entity is to be created?

All the relationships for the participating entities are many-to-many. The new associative entity has independent meaning. The new associative entity participates in independent relationships. (all of the above)

Character

Alphabetic or numeric

Relationship definition

An association between entities

Atomic Attribute

An attribute that cannot be further subdivided. It displays ATOMICITY.

Simple Attribute

An attribute that cannot be subdivided. Example: age, sex, marital status.

Optional Attribute

An attribute that does not require a value; it can be left empty. Example: Not all students have a middle name or phone number (so these would not be required).

Required Attribute

An attribute that must have a value; it cannot be left empty. In the Crow's Foot Model, these are in bold to indicate that there must be data entry. Example: students are assumed to all have a last and first name (so these would be required).

Existence-dependent

An entity that can exist in the database only when it is associated with another related entity occurrence. It has a mandatory FK attribute that cannot be null. Example: PARENT has Child (so the child entity is existence-dependent on the PARENT entity bc it is impossible for a child to exist without the PARENT.

HAVING clause

An extension of the GROUP BY feature; it operates like WHERE in the SELECT statement. BUT: WHERE applies to columns and expressions for individual rows. HAVING is applied to the output of a GROUP BY operation.

database system

An organization of components that defines and relates the collection, storage, management, and use of data in a database environment

What is a derived attribute, and how is it different from a stored attribute?

Answer: A derived attribute is an attribute whose value can be calculated from other related attributes. A derived attribute is not stored in the physical table which is eventually created from the ERD. A stored attribute, as its name implies, is stored as a column in the physical table.

How is a strong entity different from a weak entity?

Answer: A strong entity type exists independently of any other entities. A weak entity type depends on another (strong) entity type. When an instance of the strong entity type no longer exists, any weak entity instances which depend upon the strong entity cease to exist.

What is an associative entity? What four conditions should exist in order to convert a relationship to an associative entity?

Answer: An associative entity is 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. Often, a many-to-many relationship is converted to an associative entity. The following four conditions should exist in order to do this: 1. All the relationships for the participating entities types are many relationships. 2. The resulting associative entity has independent meaning. 3. The associative entity has one or more attributes other than the identifier. 4. The associative entity participates in one or more relationships independent of the entities in the associative relationship.

What is the difference between an entity type and an entity instance?

Answer: An entity type is a collection of entities that share common properties. An entity instance is a single occurrence of an entity type. So, for example, STUDENT is an entity type and John Smith is an entity instance.

What are the three different degrees of relationship?

Answer: The thee possible degrees are: Unary (an instance of one entity is related to an instance of the same entity type), Binary (an entity instance of one type is related to an entity instance of another type) and Ternary (instance of three different types participate in a relationship).

Base tables AKA

Base relations--are actually created and stored in the DBMS

Unified Modeling Language (UML)

Based on OO concepts, is a set of diagrams and symbols used to graphically model a system.

Key-Value model

Based on a structure composed of two elements: a key and a value in which every key has a corresponding value or set of values.

Iterative Process

Based on repetition of processes and procedures. This is when you make an ER diagram. Have to identify business rules, main entities and relationships, develop an initial ERD, identify attributes and PKs, and then revise/review the ERD.

Generalization

Bottom-up process or identifying a higher-level, more generic entity supertype from lower-level subtypes. Based on grouping the common characteristics and relationships of the subtypes. Example: might identify piano, violin and guitar. You could identify a "string instrument" entity supertype as a common characteristic for the subtypes.

Ch. 2 - BI Systems

Business intelligence systems are information systems used to support management decisions by producing information for assessment, analysis, planning, and control.

Aggregate Functions

COUNT, MIN, MAX, SUM, AVG Used with SELECT to return mathematical summaries on columns

ORDER BY

Can organize Query results using this clause. Ex. ORDER BY D.Dname, E.Lname, E.Fname;

Business intelligence

Captures and processes business data to generate information that support decision making

Business intelligence

Captures and processes business data to generate information that supports decision making.

Attribute definition

Characteristics of entities.

EXISTS

Checks whether a subquery returns any rows

BETWEEN

Checks whether an attribute value is within a range

LIKE

Checks whether an attribute value matches a given string pattern

IN

Checks whether an attribute value matches any value within a value list

Which of the following criteria should be considered when selecting an identifier?

Choose an identifier that is stable. Choose an identifier that will not be null. Choose an identifier that doesn't have large composite attributes. (All of the above)

Database management system DBMS

Collection of programs; manages the database structure; controls access to data stored in the database.

Ch. 1 - What is it called when more than one column in a table is combined to form a primary key?

Composite key

Computerized file systems

Computer-based system that track data and produce required reports.

Entity Subtype

Contain their own unique characteristics but are directly related to a supertype and inherit its attributes too. Example: a PILOT is a subtype of EMPLOYEE. also MECHANIC and ACCOUNTANT (these have unique attributes). CLERK is not acceptable bc it only satisfies that it is an identifiable kind of employee but none of the attributes are unique to just clerks.

General purpose databases

Contains a wide variety of data used in multiple disciplines

General-purpose database

Contains a wide variety of data used in multiple disciplines.

Ch. 1 - Row

Contains data about a particular instance

Database Developer

Create and maintain database based applications, programing, database fundamentals, SQL

Cloud database

Created and maintained using cloud data services that provide defined performance measures for the database

Cloud database

Created and maintained using cloud data services that provide defined performance measures for the database.

CREATE SCHEMA AUTHORIZATION

Creates a db schema

CREATE TABLE

Creates a new table in the user's db schema

Currency

Currency values and numeric data used in mathematical calculations

Data dependence

Data access changes when data storage characteristics change. Significant for difference between logical and physical format.

Distributed database

Data is distributed across different sites.

Centralized database

Data is located at a single site.

What are some of the guidelines for good data names of objects in general?

Data names always should: 1. Relate to the business not technical characteristics. Student would be a good name but not filest023. 2. Be meaningful so that the name tells what the object is about 3. Be unique 4. Be readable 5. Be composed of words taken from an approved list 6. Be repeatable 7. Follow a standard syntax

Data independence

Data storage characteristics are changed without affecting the program's ability to access the data.

Data independence

Data storage characteristics is changed without affecting the program's ability to access the data

unstructured data

Data that exists in its original, raw state; that is, in the format in which it was collected

semistructured data

Data that has already been processed to some extent

Ch. 2 - Data Warehouse

Database systems that have data, programs, and personnel that specialize in the preparation of data for BI processing.

Ch. 2 - Operational Database

Database that stores the company's current day-to-day transaction data

Date/Time

Date and Time values for the years 100 through 9999

Structured Query Language (SQL)

De facto query language and data access standard supported by the majority of DBMS vendors

Structured query language

De facto query language and data access standard supported by the majority of DBMS vendors.

FOREIGN KEY

Defines a FK for a table

PRIMARY KEY

Defines a PK for a table

DROP TABLE command does what?

Deletes a table from the database

Relationship

Describes an association among entities. Data models use 3 types: one to many, one to one, and many to many.

Operational database

Designed to support a company's day to day operations

Operational database

Designed to support a company's day-to-day operations.

Database Analyst

Develop Databases for decision support reporting, SQL, query optimization, data warehouses

Data inconsistency

Different versions of the same data appear in different places.

Web mining

Discovers patterns in a less structured DBMS

Vertical partitioning

Distribution of the columns of a logical relation into several separate physical tables

Horizontal partitioning

Distribution of the rows of a logical relation into several separate tables

The ERM forms the basis of an ____, which represents _____.

ERD; the conceptual database as viewed by the end user.

Security management

Enforces user security and data privacy

Performance tuning

Ensures efficient performance of the database in terms of storage and access speed

Performance tuning

Ensures efficient performance of the database in terms of storage and access speed.

NOT NULL

Ensures that a column will not have null values

Database main components

Entities, attributes, and relationships

ERD

Entity Relationship Database; describes the data and map processes to data requirements

Specialization Hierarchy

Entity supertypes and subtypes are organized into this; it depicts the arrangement of higher-level entity supertypes (parent entities) and lower-level entity subtypes (child entities). A subtype can only exist with the context of a supertype and every subtype can only have one supertype to which it is directly related. But it can have many levels of supertype/subtype relationships so a supertype can have many subtypes.

One-to-One (1:1 or 1..1)

Ex. Each store is managed by one employee, and each manager employee manages one store.

One-to-Many (1:M or 1..*)

Ex. Painter paints many paintings, but each painting is made by one painter.

Ch. 2 - ETL System

Exact, Transform, and Load System - cleans and prepares data for BI processing, then the data is stored in the data warehouse DBMS.

aliases or tuple variables

Example FROM Employee AS E, Employee AS S WHERE E.Super_ssn=S.ssn;

Unary Relationship

Exists when an association is maintained with a single entity. AKA a recursive relationship because the entity has a relationship with itself. Example: EMPLOYEE manages EMPLOYEE (like if it is a manager then it is technically an employee that manages employees). (1:1 relationship with itself)

data redundancy

Exists when the same data is stored unnecessarily at different places

Transitive Dependency

Exists when there are functional dependencies such that X-->Y, Y-->Z, and X is the PK. So the dependency X -->Z is a transitive dependency bc X determines the value of Z via Y. *more difficult to identify among a set of data. AKA the signaling dependency.

Partial Dependency

Exists when there is a functional dependence in which the determinant is only part of the PK. Example: (A,B) is the PK, then the functional dependence B --> C is a partial dependency because only part of the PK (B) is needed to determine the value of C. *these are straightforward and easy to identify

Ternary Relationship

Exists when three entities are associated. (higher degrees exist like four-degree relationship, but they are rare). Example: DOCTOR, PATIENT, AND DRUG and the verb for the relationship is prescribes. DOCTOR writes many PRESCRIPTIONs PATIENT receives many PRESCRIPTIONs DRUG appears in many PRESCRIPTIONs

Binary Relationship

Exists when two entities are associated in a relationship. The most common type of relationship. Higher-order relationships, like ternary, are decomposed into equivalent binary relationships whenever possible to simplify the conceptual design. Example: PROFESSOR teaches one or more CLASSes.q

Cardinality

Expresses the minimum and maximum number of entity occurrences associated with one occurrence of the related entity. Indicated by putting numbers by the entities like (x,y). X is the minimum number of associated entities, and y is max. Example: A PROFESSOR teaches a CLASS. Cardinality: PROFESSOR(1,1) and CLASS (1,4) This means that each professor teaches up to four classes, so the PK occurs at least once and no more than 4 times as FKs in the CLASS table.

A business rule is a statement of how a policy is enforced or conducted.

FALSE

A cardinality constraint tells what kinds of properties are associated with an entity.

FALSE

A relationship instance is an association between entity instances where each relationship instance includes exactly one entity from each participating entity type.

FALSE

A ternary relationship is equivalent to three binary relationships.

FALSE

An entity type on which a strong entity is dependent is called a covariant entity.

FALSE

An example of a term would be the following sentence: "A student registers for a course."

FALSE

Business rules are formulated from a collection of business ramblings.

FALSE

Data names do not have to be unique.

FALSE

In an E-R diagram, strong entities are represented by double-walled rectangles.

FALSE

It is not permissible to associate attributes with relationships.

FALSE

Most systems developers believe that data modeling is the least important part of the systems development process.

FALSE

Some examples of attributes are: eye_color, weight, student_id, student.

FALSE

The degree of a relationship is the number of attributes that are associated with it

FALSE

The intent of a business rule is to break down business structure

FALSE

The maximum criminality of a relationship is the maximum number of instances of entity B that may be associated with each instance of entity A.

FALSE

The name used for an entity type should never be the same in other E-R diagrams on which the entity appears.

FALSE

The purpose of data modeling is to document business rules about processes.

FALSE

The relationship among the instances of three entity types is called a unary relationship.

FALSE

When systems are automatically generated and maintained, quality is diminished.

FALSE

Well designed databases:

Facilitate data management, generate accurate and valuable information.

Well designed database

Facilitates data management, Generates accurate and valuable information

Key

Fields that are used to sort and retrieve information. It holds a data item that is unique for each record. The key is used so that not all the data is read.

Structural independence

File structure is changed without affecting the application's ability to access the data

Structural independence

File structure is changed without affecting the application's ability to access the data.

Double

Floating point decimal numbers from -1.797 x 10308 to 1.797 x 10308 with 10 digits of precision.

A department can have more than one employee.

For the relationship represented in the figure below, which of the following is true?

GROUP BY

Groups the selected rows based on one or more attributes

Database Consultant

Help companies leverage database technologies to improve business processes and achieve specific goals, database fundamentals, data modeling, database design, SQL, DBMS, hardware vendor specific technologies, etc.

Core

Implemented by RDMS vendors that comply with SQL--must have specifications in SQL

Sparse data

In NoSQL DBs, the case in which the number of attributes is very large but the number of actual data instances is low.

Eventual consistency

In NoSQL, means updates to the DB will propogate through the system and evebtually all data copies will be consistent.

Ternary

In the following diagram, what type of relationship is depicted?

It depicts a unary relationship. It depicts a many-to-many relationship. There is an associative entity. (All of the above)

In the following diagram, which is true?

Each patient has one or more patient histories. Each patient history belongs to one and only one patient. (both A and C)

In the following diagram, which of the answers below is true?

islands of information

In the old file system environment, pools of independent often duplicated, and in consisted data created and managed by different departments

File System Redux: Modern End User Productivity Tools

Includes spreadsheet programs such as Microsoft Excel

Which of the following is NOT a characteristic of a good business rule?

Inconsistent

Disadvantages of Database Systems

Increased costs, Management complexity, Maintaining currency, Vendor dependence, Frequent upgrade/replacement cycles

Advantages of DBMS

Increased end-user productivity; data sharing, security, access, and decision making.

Default join

Inner joint

INSERT

Inserts row(s) into a table

Role of the DBMS

Intermediary between user and database; enables data sharing; present end-user integrated view of data; receives and translates application requests into operations; hides integral complexity from programs and users.

What does * mean?

It means "all the attributes"

Boolean

It only requires one byte of data. This is often used for fields that requires answers such as Yes/No - Y or N, True/False - T or F, Gender - M or F.

Entity naming conventions

It's a noun; usually written in all CAPS.

Problems with File System Data Processing

Lengthy development times, Difficulty of getting quick answers, Complex system administration, Lack of security and limited data sharing, Extensive programming

Query language

Lets the user specify what must be done without having to specify how

Record

Logically connected set of one or more fields that describe a person, place, or thing. E.g. Customer name, address, phone number, date of birth.

Database Systems

Logically related data stored in a single logical data repository, DBMS eliminates most of file system's problems, Current generation DBMS software

Physical model

Lowest level of abstraction. Describes the way data are saved on storage media.

Composite Key

Made by joining two or more fields together. Used when no item in any field can be guaranteed to be unique.

Database Administrator

Manage and maintain DBMS and databases, database fundamentals, SQL, vendor courses

Typical Data Migration Order

Manual -> Conventional Files/Spreadsheets -> Database

Evolution of File System Data Processing

Manual File Systems -> Computerized File Systems -> File System Redux: Modern End User Productivity Tools

Internal model

Maps the conceptual model to the DBMS. Its what the DBMS "sees" and in relational DB is composed of SQL commands.

Virtual relations

May or may not correspond to actual file--made through "create view" statement

Software Independence

Means that the model in use does not depend on the DBMS software used to implement the model.

Hardware Independence

Means that the model in use is not dependent on the hardware used to implement the model.

Optional Entity

Minimum cardinality is 0.

Raw Data

Not yet been processed to reveal the meaning

Raw data

Not yet been processed to reveal the meaning.

Mandatory Participation

One entity occurrence requires a corresponding entity occurrence in a particular relationship. If no optionality symbol is depicted with the entity in crow's foot notation, then the entity is assumed to exist in a mandatory relationship with the related entity.

Ch. 2 - OLTP

Online transaction processing system is used to record all sales transactions of the company.

Which of the following is an entity type on which a strong entity depends?

Owner Member Attribute D) None of the above is the answer

DROP TABLE

Permanently deletes a table (and its data)

COMMIT

Permanently saves data changes

Logically related data stored in a single logical data repository

Physically distributed among multiple storage facilities

Strong Relationship

Primary key of related entity contains a primary key component of the parent entity

Information

Produced by processing data, Reveals the meaning of data, Enables knowledge creation, Should be accurate, relevant, and timely to enable good decision making

Information

Produced by processing data. Reveals meaning of data; should be accurate, relevant, and timely.

Ch. 1 - What does a foreign key do?

Provides the link between two tables, creating a relationship

Ch. 2 - What is Microsoft Access's GUI style called?

QBE - query by example

Database access languages and application programming interfaces

Query language, Structured query language (SQL)

Data

Raw Facts, Have little meaning unless its been organized in some logical manner, Building block of information

End-user data

Raw facts of interest to end user.

Data

Raw facts, such as a telephone number, birth date, customer name, and year-to-date sales value. Little meaning unless organized in some logical manner.

Normalizing the table structure will do what?

Reduce the data redundancies. If repeating groups exist, they must be eliminated by making sure that each row defines a single entity. Dependencies must be identified to diagnose the normal form.

Time-variant data

Refer to data whose values change over time and for which you must keep a history of the data changes. For example: GPA or bank account balance change over time. Whereas, DOB or SSN are not time variant.

Relational Operator

Refers to the characters or symbols indicating the relationship between two expressions. They are used for simple queries.

Database model

Refers to the implementation of a data model in a specific database system.

Relational Database Management System

Relational Database Management System is a program that enables users to create and manage data

Wildcard Operators

Represent one or more unknown characters. The question mark (?) substitutes for one character and the asterisk (*) substitutes for a number of charcters.

Conceptual Model

Represents a global view of an entire DB by an entire organization. A.k.a conceptual schema. Most widely used model is ER with ERD graphics.

Extensible Markup Language (XML)

Represents data elements in textual format

Extensible markup language XML

Represents data elements in textual format.

AND

Requires both the first and second query to be true.

OR

Requires either the first or second query to be true.

MAX

Returns the maximum attribute value found in a given column

MIN

Returns the minimum attribute value found in a given column

COUNT

Returns the number of rows with non-null values for a given column

Rules of precedence

Rules that establish the order in which computations are completed. PEMDAS

Retrieve the b-date and address of employee(s) whose name is John B. Smith

SELECT Bdate, Address FROM EMPLOYEE WHERE Fname='John' AND Minit='B' AND Lname='Smith';

SELECT Statement

SQL's one basic stmt for retrieving info from DB.

Islands of information

Scattered data locations, Increases the probability of having different versions of the same data

Islands of information

Scattered data locations. Increases the probability of having different versions of the same data.

Database

Shared, integrated computer structure that stores a collection of: End user data and metadata

Multiuser access control

Sophisticated algorithms ensure that multiple users can access the database concurrently without compromising its integrity

Ch. 1 - Column

Stores a characteristic common to all rows

Analytical database

Stores historical data and business metrics used exclusively for tactical or strategic decision making.

Workgroup databases

Supports a small number of users or a specific department

Workgroup database

Supports a small number of users or a specific department.

Enterprise database

Supports many users across many departments

Enterprise database

Supports many users across many departments.

Multi-user database

Supports multiple users at the same time.

Single user database

Supports one user at a time, Desktop Database

Single-user database

Supports one user at a time.

A fact is an association between two or more terms.

TRUE

A good data definition is always accompanied by diagrams, such as the entity-relationship diagram.

TRUE

A single occurrence of an entity is called an entity instance.

TRUE

Data modeling is about documenting rules and policies of an organization that govern data.

TRUE

Data names should always relate to business characteristics.

TRUE

Data, rather than processes, are the most complex aspects of many modern information systems

TRUE

Enforcement of business rules can be automated through the use of software tools that can interpret the rules and enforce them.

TRUE

It is desirable that no two attributes across all entity types have the same name.

TRUE

One reason to use an associative entity is if the associative entity has one or more attributes in addition to the identifier.

TRUE

Participation in a relationship may be optional or mandatory.

TRUE

Relationships represent action being taken using a verb phrase.

TRUE

The E-R model is used to construct a conceptual model.

TRUE

The relationship between a weak entity type and its owner is an identifying relationship.

TRUE

The relationship between the instances of two entity types is called a binary relationship.

TRUE

When choosing an identifier, choose one that will not change its value often.

TRUE

T/F? The relationship classification is difficult to establish if you only know one side of the relationship.

TRUE.

Identifiers

The ERM uses these; they are one or more attributes that uniquely identify each entity instance. Can be the PK of a table. These are UNDERLINED in the ERD.

a composite attribute

The address attribute is

Subtype Discriminator

The attribute in the supertype entity that determines to which subtype the supertype occurrence is related. Example: EMPLOYEE_TYPE so if the EMPLOYEE_TYPE has a value of "P" then the supertype is related to the PILOT subtype.

knowledge

The body of information and facts about a specific subject

query result set

The collection of data rows returned by a query

database management system

The collection of programs that manages the database structure and controls access to the data stored in the database

Ch. 1 - What is database design?

The creation of the proper structure of database tables, the proper relationships between tables, appropriate data constraints, and other structural components of the database.

Participants

The entities that participate in a relationship. And each relationship is identified by a name that describes the relationship. The relationship is an active or passive verb for example: a student TAKES a class, a professor TEACHES a class. etc.

enterprise database

The overall company data representation, which provides support for present and expected future needs

data processing specialist

The person responsible for developing and managing a computerized file processing system

Subschema

The portion of the database "seen" by the application programs that actually produce the desired info from the data within the database.

Searching

The process of retrieving data from the database by using something relevant to the data such as a string of characters or a word

Denormalization

The process of transforming normalized relations into non-normalized physical record specifications

database design

The process that yields the description of the database structure and determines the database components. The second phase of the Database Life Cycle

multivalued attribute

The skill attribute is

Field

The smallest unit of application data recognized by system software

physical data format

The way a computer "sees"(stores) data

logical data format

The way a person views data within the context of a problem domain

derived

The years employed attribute is

Relational Schema

This is a shorthand notation for the table structure; Key attributes are underlined in the ERD.

Query language (QL)

This is a specialised language designed to allow users to access information from the database. It is the most complex method because the user must learn the language but it provides the most power and flexibility. Different DBMSs support different query languages. SQL (Structured Query Language) is a standard query language but there are different versions of it in use. Storing & retrieving.

Menu

This is often the easiest way to pose a query but is the least flexible. The DBMS presents the user with a list of options from to choose.

Query by example (QBE)

This requires the user to enter the criteria against a field. For example, if you were looking for people who lived in Eastwood, you would type 'Eastwood' in the 'Suburb' field and leave the remaining fields blank. The DBMS would then search the database and select all records that have Eastwood in the 'Suburb' field.

What is the function of the PK?

To guarantee entity integrity, not "describe" the entity.

Role of applications programmers

To translate company policies and procedures from a variety of sources into appropriate interfaces, reports, and query screens.

Specialization

Top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype. Based on grouping the unique characteristics and relationships of the subtypes. Example: you identify many entity subtypes from the original employee supertype.


Ensembles d'études connexes

HIST 1100 Final: Civil War Diplomacy

View Set

Chapter 19: Cholinergic Agonists and Anticholinergics

View Set

Chapter 6: Corporate Forms of Business Ownership

View Set

SkillsUSA - Criminal Justice Trivia Bowl Terms

View Set

SOC205 - Exam II - Chapter 8 Questions

View Set

life health and variable annuity

View Set