Database Design Exam 1

WHERE Clause with operators

= Equal To < Less Than <= Less Than or Equal To > Greater Than >= Greater Than or Equal To <> Not Equal To In additional to the comparison operators, the BETWEEN construct is available. - Selects only the stock records with a price-earnings ratio between 6 and 12: SELECT * FROM stock WHERE stkpe BETWEEN 6 AND 12; & the WHERE Clause CAN HAVE multiple conditions

foreign key

A foreign key is a set of attributes in a table that refers to the primary key of another table, linking these two tables.

GROUP BY Clause

The GROUP BY Clause is often used with aggregate functions ( COUNT( ), MAX( ), MIN( ), SUM( ), AVG( ) ) to

HAVING Clause

The HAVING clause is used for aggregation function conditions. That's because the WHERE clause cannot be used on aggregation functions. For example, list the countries that have an average PE ratio over 13.

Inserting row into table

The SQL statement INSERT is used to add rows to the table.

Schemas

The Schemas Tab, shows the available internal databases within the server.

UPDATE Statement

The UPDATE statement is used to update existing records in a table. WHERe specifies where the change will be made or else it will apply to all records

WHERE Clause

The WHERE clause of the SELECT statement restricts the table to those rows that satisfy a specified condition. In this example, we are outputting customers with a specific telephone number: SELECT * FROM `new_schema`.`customer` WHERE telephone = '817-749-5131'; or SELECT * FROM stock WHERE stkpe < 12;

NOT IN Operator

The keyword IN is used with a list to specify a set of values. Selects only the stock records NOT for UK or USA: SELECT * FROM stock WHERE natcode NOT IN ('UK','USA');

IN Operator

The keyword IN is used with a list to specify a set of values. Selects only the stock records for UK or USA: SELECT * FROM stock WHERE natcode IN ('UK','USA'); CAN ALSO be used for subqueries

Data Definition Language (DDL)

The language that allows a database administrator to define the database structure, schema, and subschema.

LIKE Operator

The operator LIKE can be used to match a string. For example, list all the firms that have the word 'Gold' in their name. use %%

TRUNCATE TABLE Statement

To delete ONLY all the data from a table, the TRUNCATE TABLE statement can be used.

primary key

a column or columns in a database table with values that uniquely identify each row or record.

SELECT Statement

allows you to choose the columns to be output in the results. In this example, we are outputing customernumber, name:

Information

data processed to increase knowledge in the person using the data

Metadata

data that describes the properties and context of user data

ORDER BY Clause

of the SELECT statement orders the sequence based on a particular column in the output results.

Database

organized collection of logically related data

DISTINCT Clause

removes duplicate values from query results, get a list of UNIQUE values

primary and foreign key diagram

see figure

The Database Approach (1 of 2)

Data models - Graphical diagram capturing nature and relationship of data - Enterprise Data Model - high-level entities and relationships for the organization - Project Data Model - more detailed view, matching data structure in database or data warehouse Entities - Noun form describing a person, place, object, event, or concept - Composed of attributes Relationships - Between entities - usually one to many

Evolution of Database Systems

Driven by four main objectives: - Need for program-data independence in order to reduce maintenance - Desire to manage more complex data types and structures - Ease of data access for less technical personnel - Need for more powerful decision support platforms

Data

stored representations of meaningful objects and events Structured: numbers, text, dates Unstructured: images, video, document

Use ALTER TABLE

to add new columns

Figure 1-11 The Range of Database Technologies: Past and Present

view figure

Components of the Database Environment

- Data modeling and design tools - automated tools used to design databases and application programs - Repository - centralized storehouse of metadata - Database Management System (D B M S) - software for managing the database - Database - storehouse of the data - Application Programs - software using the data - User Interface - text, graphical displays, menus, etc. for user - Data/Database Administrators - personnel responsible for maintaining the database - System Developers - personnel responsible for designing databases and software - End Users - people who use the applications and databases

Costs and Risks of the Database Approach

- New, specialized personnel - Installation and management cost and complexity - Conversion costs - Need for explicit backup and recovery - Organizational conflict

Querying a database

- The objective of developing a database is to make it easier to use the centralized data and be accessed by multiple users in a managed and controlled manner. - The SQL statement SELECT is used to query the rows of the table.

Database Project Team Members

Business analysts - analyze business situation and establish requirements Systems analysts - like business analysts, but also have technical expertise for overall information systems Database analysts and data modelers - analysts who focus on database Users - the "customers" communicate their needs to analysts Programmers - coders of the programs that interact with the database Database architects - establish standards for data in business units Data administrators - responsible for existing databases, ensuring data integrity and consistency Project managers - oversee the projects, manage the personnel Other technical experts - network, operating system, documentation, etc.

The Range of Database Applications

Personal Databases - Typical size in the megabytes - Intended for one user Departmental Multi-Tiered Client/Server Databases - Typical size in the gigabytes - Intended for several users, usually doesn't exceed 100, department-wide Enterprise Applications - Typical size in the gigabytes, terabytes, or even petabytes - Intended for a very large user base, company wide

Disadvantages of File Processing

Program-Data Dependence - All programs maintain metadata for each file they use Duplication of Data - Different systems/programs have separate copies of the same data Limited Data Sharing - No centralized control of data Lengthy Development Times - Programmers must design their own file formats Excessive Program Maintenance - 80% of information systems budget

Advantages of the Database Approach

Program-data independence Planned data redundancy Improved data consistency Improved data sharing Increased application development productivity Enforcement of standards Improved data quality Improved data accessibility and responsiveness Reduced program maintenance Improved decision support

Managing People Involved in Database Development

Project - a planned undertaking of related activities to reach an objective that has a beginning and an end Initiated and planned in planning stage of S D L C Executed during analysis, design, and implementation Closed at the end of implementation.

The Database Approach (2 of 2)

Relational Databases Database technology involving tables (relations) representing entities and primary/foreign keys representing relationships (see Figure 1-17)

The Database Development Process

S D L C System Development Life Cycle Detailed, well-planned development process Time-consuming, but comprehensive Long development cycle Prototyping Rapid application development (R A D) Cursory attempt at conceptual data modeling Define database during development of initial prototype Repeat implementation and maintenance activities with new prototype versions

Aggregating Functions

SQL has built-in aggregations, such as COUNT, AVG, MIN, MAX.

Defining a table

SQL uses the CREATE statement to define a table in a database.

As Clause

The AS clause can be used to rename a column in the SQL.

DELETE Statement

The DELETE statement deletes rows from a table. For example, to delete all rows from a table: DELETE FROM stock; This example deletes all rows because there's no WHERE clause specified in the DELETE statement.

DROP TABLE Statement

The DROP TABLE statement deletes the whole table, both the data and the table definition are deleted.