Exam 1

-production of accurate, relevant, and timely information is the key to good decision making -good decisions require good information that is derived from raw facts -key to business survival in a global market

-spur of the moment question. ex: What was the dollar volume of sales by product during the past six months?

are the people who use the application programs to run the organization's daily operations. For example, salesclerks, supervisors, managers, and directors are all classified as end users. High-level end users employ the information obtained from the database to make tactical and strategic business decisions

design and implement the application programs. They design and create the data entry screens, reports, and procedures through which end users access and manipulate the database's data.

The word data covers the collection of facts stored in the database. Because data are the raw material from which information is generated, the determination of what data are to be entered into the database and how those data are to be organized is a vital part of the database designer's job.

the result of processing data to reveal its meaning. Accurate, relevant, and timely information is the key to good decision-making, and good decision-making is the key to organizational survival in a global environment.

data about data ex: author, date created and date modified and file size are examples of very basic document metadata

The DBMS stores definitions of -the data elements and their relationships (metadata) in a data dictionary -The DBMS uses the data dictionary to look up the required data component structures and relationships, thus relieving you from having to code such complex relationships in each program -Additionally, any changes made in a database structure are automatically recorded in the data dictionary

- Designed by the database designer. - Database design defines the database structure. A well-designed database facilitates data management and generates accurate and valuable information. A poorly designed database can lead to bad decision making, and bad decision making can lead to the failure of an organization.

Data inconsistency exists when different versions of the same data appear in different places ex: suppose you change an agent's phone number or address in the AGENT file. If you forget to make corresponding changes in the CUSTOMER file, the files contain different data for the same agent. Reports will yield inconsistent results that depend on which version of the data is used.

-The collection of data rows that are returned by a query (question/ specific request issued to the DBMS for data manipulation) -When you do an ad hoc query, a query result set is returned

- Transactions such as product or service sales, payments, and supply purchases reflect critical day-to-day operations. Such transactions must be recorded accurately and immediately. A database that is designed primarily to support a company's day-to-day operations is classified as an operational database (sometimes referred to as a transactional or production database

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

-When the multiuser database supports a relatively small number of users (usually fewer than 50) or a specific department within an organization

o Anything (a person, a place, a thing, or an event) about which data are to be collected and stored o Represents a particular type of object in the real world o Each entity occurrence is unique and distinct

o Characteristic of an entity o For example, a CUSTOMER entity would be described by attributes such as customer last name, customer first name, customer phone, customer address, and customer credit limit

A character or group of characters (alphabetic or numeric) that has a specific meaning. A field is used to define and store data.

: A logically connected set of one or more fields that describes a person, place, or thing. For example, the fields that constitute a record for a customer might consist of the customer's name, address, phone number, date of birth, credit limit, and unpaid balance

Each row in a relation is called a tuple

Describes an association among entities

o One-to-many (1:M) relationship: A painter paints many different paintings, but each one of them is painted by only one painter. Thus, the painter (the "one") is related to the paintings (the "many").

o Many-to-many (M:N) relationship: An employee may learn many job skills, and each job skill may be learned by many employees.

o One-to-one (1:1) relationship: A retail company's management structure may require that each of its stores be managed by a single employee. In turn, each store manager, who is an employee, manages only a single store.

- A restriction placed on the data. Constraints are important because they help to ensure data integrity. Constraints are normally expressed in the form of rules. For example: o An employee's salary must have values that are between 6,000 and 350,000. o A student's GPA must be between 0.00 and 4.00. o Each class must have one and only one teacher.

- Think of a relation (sometimes called a table) as a matrix composed of intersecting rows and columns. Each row in a relation is called a tuple. Each column represents an attribute. -RELATIONS TRANSLATE INTO DATA

- Defined as the condition in which all of the data in the database are consistent with the real-world events and conditions. In other words, data integrity means that: o Data are accurate—there are no data inconsistencies. o Data are verifiable—the data will always yield consistent results

o A brief, precise, and unambiguous description of a policy, procedure, or principle within a specific organization. o help to create and enforce actions within that organization's environment o Know what they are and where they are derived from -- communication rule between the business and the designers

- In short, the relational model enables you to view data logically rather than physically. - The practical significance of taking the logical view is that it serves as a reminder of the simple file concept of data storage. - Logical simplicity tends to yield simple and effective database design methodologies.

o A noun in a business rule will translate into an entity in the model o A verb (active or passive) associating nouns will translate into a relationship among the entities

o Logical: can only have true or false (yes or no) values -- 2 values o Character: can contain any character or symbol not intended for mathematical manipulation -- aka text or string data o Date: contain calendar dates stored in a special format known as the Julian date format o Numeric: data on which you can perform meaningful arithmetic procedures

- Any attribute in a specific row whose value directly determines other values in that row

- Can be defined most easily this way: the attribute B is functionally dependent on A if A determines B. More precisely: o The attribute B is functionally dependent on the attribute A if each value in column A determines one and only one value in column B.

Any attribute that is part of a key

any key that uniquely identifies each row. In short, the superkey functionally determines all of a row's attributes. o The PK is a super key o But the Super Key doesn't have to be a PK

can be described as a superkey without unnecessary attributes, that is, a minimal superkey. Using this distinction, note that the composite key

o A candidate key selected to uniquely identify all other attribute values in any given row o Cannot contain null entries o Each table must have a primary key o a primary key is a superkey as well as a candidate key o identify primary keys by underlying

an attribute whose values match the primary key values in the related table. More specifically, the primary key of one table appears as the foreign key in a related table.

a key that is used strictly for data retrieval purposes -. In that case, the primary key is the customer number; the secondary key is the combination of the customer's last name and phone number. Keep in mind that a secondary key does not necessarily yield a unique outcome

it might take more than a single attribute to define functional dependence; that is, a key may be composed of more than one attribute

- In SQL, refers to the absence of an attribute value (unknown/missing). Note: a null is NOT blank.

They can uniquely identify any given records

- Means that if the foreign key contains a value, that value refers to an existing valid tuple (row) in another relation. - The Foreign Key must match the Primary Key (database design rule #2)

diagramming techniques and conventions (i.e., how relationships are illustrated)

o The column's range of permissible values o Because the STU_GPA values are limited to the range 0-4, inclusive, the domain is [0,4]

- an attribute that cannot be subdivided - o For example, age, sex, and marital status would be classified as simple attributes. To facilitate detailed queries, it is wise to change composite attributes into a series of simple attributes.

- has only 1 value -o For example, a person can have only one Social Security number, and a manufactured part can have only one serial number. Keep in mind that a single-valued attribute is not necessarily a simple attribute.

: expresses the minimum and maximum number of entity occurrences associated with one occurrence of the related entity. In the ERD, cardinality is indicated by placing the appropriate numbers beside the entities, using the format (x,y).

used to describe the relationship classification

used to label any condition in which one or more optional relationships exist

- attribute that has different data (solution: NEW ATTRIBUTE) - o An attribute that can be further subdivided to yield additional attributes. For example, the attribute ADDRESS can be subdivided into street, city, state, and zip code. Similarly, the attribute PHONE_NUMBER can be subdivided into area code and exchange number

-attributes that can have many values -o For instance, a person may have several college degrees, and a household may have several different phones, each with its own number. Similarly, a car's color may be subdivided into many colors (that is, colors for the roof, body, and trim).

if it can exist in the database only when it is associated with another related entity occurrence. In implementation terms, an entity is existence-dependent if it has a mandatory foreign key—that is, a foreign key attribute that cannot be null.

If an entity can exist apart from all of its related entities; that entity is referred toas a strong entity or regular entity.

In almost all of your work , a key (aka identifier) would contain 1 attribute

- exists when an association is maintained within a single entity o Ex: A COURSE is a prerequisite to another COURSE

-exists when two entities are associated. o Ex: PROFESSOR teaches COURSE

-exists when three entities are associated o Ex: A CONTRIBUTOR contributes money to a FUND from which a RECIPIENT receives money

o Strong: exists by itself, - - existent independent -- aka regular o Weak: exists if the PK of the related entity does not contain a PK component of the parent entity -- aka non-identifying relationship -- Associative Entity: used to implement a M:N relationship between two or more entities. This associative entity (also known as a composite or bridge entity) is composed of the primary keys of each of the entities to be connected

o value that is derived from another attribute o 2 ways to have a derived attribute: 1. can either be created on the fly by an algorithm 2. stored in the database

o Strong (solid line) o Weak (dashed line)

-: The UNION statement combines rows from two or more queries without including duplicate rows o the UNION statement combines the output of two SELECT queries

-If SaleCo's management wants to know how many customers are on both the CUSTOMER and CUSTOMER_2 lists, a UNION ALL query can be used to produce a relation that retains the duplicate rows. o Like the UNION statement, the UNION ALL statement can be used to unite more than just two queries

PROJECT yields all values for selected attributes. In other words, PROJECT yields a vertical subset of a table.

-yields values for all rows found in a table that satisfy a given condition -in other words, SELECT yields a horizontal subset of a table

yields only the rows that appear in both tables. As was true in the case of UNION, the tables must be union-compatible to yield valid results. For example, you cannot use INTERSECT if one of the attributes is numeric and one is character-based.

yields all rows in one table that are not found in the other table; that is, it subtracts one table from the other.

yields all possible pairs of rows from two tables—also known as the Cartesian product. Therefore, if one table has six rows and the other table has three rows, the PRODUCT yields a list composed of 6 × 3 = 18 rows.