Term
|
Definition
This is your main file that encompasses the entire database and that is saved to your hard-drive or floppy disk.
DTFD |
|
|
Term
|
Definition
A table is a collection of data about a specific topic..
DTFD |
|
|
Term
|
Definition
Fields are the different categories within a Table. Tables usually contain multiple fields.
DTFD |
|
|
Term
|
Definition
Datatypes are the properties of each field. A field only has 1 datatype. (text, number…)
DTFD |
|
|
Term
|
Definition
| One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key does not allow Null values and must always have a unique value. A primary key is used to relate a table to foreign keys in other tables. |
|
|
Term
|
Definition
Normalization is necessary because:
- Multiple relationships between data columns can exist in the same table
- The relationships can vary depending on the rows
- Result: modification & insertion anomalies |
|
|
Term
|
Definition
- First normal form (1NF) sets the very basic rules for an organized database: Eliminate duplicative columns from the same table.
- Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key). |
|
|
Term
|
Definition
- Second normal form (2NF) further addresses the concept of removing duplicative data: Meet all the requirements of the first normal form.
- Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
- Create relationships between these new tables and their predecessors through the use of foreign keys. |
|
|
Term
|
Definition
- Third normal form (3NF) is a database principle that allows you to cleanly organize your tables by building upon the database normalization principles provided by 1NF and 2NF.
-There are two basic requirements for a database to be in third normal form:
+Already meet the requirements of both 1NF and 2NF
+Remove columns that are not fully dependent upon the primary key |
|
|
Term
|
Definition
- Finally, fourth normal form (4NF) has one additional requirement: Meet all the requirements of the third normal form.
- A relation is in 4NF if it has no multi-valued dependencies. |
|
|
Term
|
Definition
| - Takes the system longer to retrieve data linked (“joined”) across multiple tables, so database designer may selectively decide not to normalize to improve speed |
|
|
Term
|
Definition
| a primary key of one relation that is placed in a second relation |
|
|
Term
|
Definition
combinations of attributes that can uniquely identify a row (e.g. first name + last name + date of birth
- A type of key... |
|
|
Term
|
Definition
- A database’s way of enforcing that orphans are not created
- The foreign key in any referencing table must always refer to a valid row in the referenced table.
- Ensures that the relationship between two tables remains synchronized during updates and deletes. |
|
|
Term
|
Definition
- The relationship that one table can have with another table
+ Many-to-many
+ Many-to-one / one-to-many
+ One-to-one |
|
|
Term
|
Definition
1. Modifying the structure of the database itself (Data Definition Language / DDL commands)
2. Working with the content of the database
(Data Manipulation Language / DML) |
|
|
Term
| Modifying the structure of the database itself (Data Definition Language / DDL commands) |
|
Definition
CREATE DATABASE - Creates a new database
ALTER DATABASE - Modifies the database
DROP DATABASE - Drops (deletes) a database
ALTER TABLE - Modifies the table
DROP TABLE - Drops (deletes) a table
CREATE TABLE - Creates a new table |
|
|
Term
Working with the content of the database
(Data Manipulation Language / DML) |
|
Definition
SELECT - Retrieves data from the database
FROM - Identifies where the data are
INSERT - Inserts new data into the database
UPDATE - Updates existing data in the database
DELETE - Deletes existing data from the database |
|
|
Term
|
Definition
- A wildcard character can be substituted for any other character(s) in a string.
- % = for 0 to many characters
- _ (underscore) = for a single character
- Used with the LIKE statement |
|
|
Term
|
Definition
- But what if you decide you don’t want all the patient data from the Patients table? You only want patients named Smith.
SELECT * FROM Patients
WHERE Patients.LastName like ‘smith’; |
|
|
Term
|
Definition
- These let you specify patterns to match
+ LIKE ‘smith’ – retrieves SMITH
+ LIKE ‘smith%’ – the % would allow retrieval of SMITH, SMITHE, SMITHSON – anything that started with SMI
- = vs. LIKE: = sign is used for exact match. Like allows use of wildcards; = does not - wildcards do not work when = sign is used. |
|
|
Term
|
Definition
To select all data from a table named PATIENTS except those who have the last name of Smith:
SELECT * FROM Patients
WHERE Patients.LastName not like ‘Smith’; |
|
|
Term
|
Definition
To select all data from a table named PATIENTS who have the last name of Smith AND who were born in 1992:
SELECT * FROM Patients
WHERE (Patients.LastName like ‘Smith’ AND Patients.DateOfBirth like ‘%1992%’); |
|
|
Term
|
Definition
To select all data from a table named PATIENTS who have the last name of Smith OR have the last name of Jones:
SELECT * FROM Patients
WHERE (Patients.LastName like ‘Smith’ OR Patients.LastName like ‘Jones’); |
|
|
Term
|
Definition
ORDER BY is used to sort the results
Syntax:
SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC |
|
|
Term
|
Definition
What if you wanted to know how many different last names were in your Patients table? This won’t work because you don’t want all the names, just one entry for each name.
SELECT DISTINCT returns only one row for each name, no matter how many times that name is present
SELECT DISTINCT Patients.LastName from Patients
ORDER BY LastName |
|
|
Term
|
Definition
Use the INSERT INTO statement to add a new record (row) to a table
INSERT INTO Patients
(Patients.LastName, Patients.DOB)
Values
(‘Smith’, ‘08211959’); |
|
|
Term
|
Definition
Drop is used to delete a table
Syntax:
Drop Table Patients; |
|
|
Term
|
Definition
Update is used to modify an existing record
UPDATE Patients
SET Patients.FirstName=’Kimberly'
Where Patients.FirstName=’Kim' AND Patients.LastName=’Smith'; |
|
|
Term
|
Definition
CREATE TABLE is used to create a table
Syntax:
Create table {table name}{columns}
Create table Patients
(IDNo varchar (20) primary key,
LastName varchar(20),
Age Int
DateofBirth (Date); |
|
|
Term
|
Definition
- Perform a calculation on a set of values - result is single value
- If a value is null (empty) it will be ignored by all aggregate functions except for COUNT
- AVG() - Returns the average value
- COUNT() - Returns the number of rows
- FIRST() - Returns the first value
- LAST() - Returns the last value
- MAX() - Returns the largest value
- MIN() - Returns the smallest value
- SUM() - Returns the sum |
|
|
Term
|
Definition
1. Compute the FROM and WHERE clauses.
2. Group by the attributes in the GROUPBY
3. Compute the SELECT clause: grouped attributes and aggregates. |
|
|
Term
|
Definition
- Stored procedures are sets of SQL statements that can be used by other programs and executed like programs
1. Write your query and get it working:
SELECT *
FROM Patients;
2. Add stored procedure syntax:
DELIMITER $$
CREATE PROCEDURE GetPatients
BEGIN
SELECT *
FROM Patients
END $$ |
|
|
Term
|
Definition
Use the CALL statement followed by the stored procedure’s name
CALL GetPatients; |
|
|
Term
|
Definition
- A trigger is a procedure that initiates an action (i.e., fires an action) when a specific event (INSERT, DELETE or UPDATE) occurs
- Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. |
|
|
Term
|
Definition
Step 1. Declaring the variables
DECLARE @MinAge INT
Step 2. Setting the Variable Values
SET @MinAge = 70
Step 3. Using the Variables in a Query
SELECT *
FROM PATIENTS
WHERE (Age BETWEEN @MinAge AND @MaxAge); |
|
|
