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.
