Exam Topics: IS481 Database Management and Organization
Fall 2007

Chapter 6: Physical Design

Terminology and definitions: define and give example of:
A relation: A named two dimensional table of data.
A field: The smallest unit of named application data recognized by system software.
A data type: A detailed coding scheme recognized by system software, such as a database management system, for representing organizational data.
Index: A table or other data structure used to determine the location of rows in a file that satisfy some condition.
Compute Usage analysis (fig 6-1)
VARCHAR2: Variable-length character data with a maximum length of 4000 characters; you must enter a maximum field length (e.g., VARCHAR2 (30) for a field with a maximum length of 30 characters). A value less than 30 characters will consume only the required space.
CHAR: Fixed length character data with a maximum length of 2000 character; default length is 1 character (e.g. CHAR 5) for a field with a fixed length of 5 characters, capable of holding a value from 0 to 5 characters long.
LONG: Capable of storing up to 2 gigabytes of one variable length character data field (e.g., to hold a medical instruction or a customer comment).
NUMBER: Positive and negative numbers in the range 10^-130 to 10^126; can specify the precision (total number of digits to the left and right of the decimal point) (e.g., NUMBERS (5) specifies an integer field with a maximum of 5 digits and NUMBERs (5,2) specifies a field with no more than 5 digits and exactly, 2 digits to the right of the decimal point.)
INTEGER: Positive and negative integers with up to 38 digits (same as SMALL INT)
DATE Any date from January 1, 4712 B.C to December 31, 4712 A.D; date stores the century, year, month, day, hour minute, and second.
BLOB: Binary large object, capable of storing up to 4 gigabytes of binary data (e.g., a photograph or a sound clip).
Denormalization? Example: The process of transforming normalized relations into unnormalized physical record specifications.
Data Partitioning (vertical, horizontal): is a division of a logical database or its constituting elements into distinct independent parts. Database partitioning is normally done for manageability, performance or availability reasons.
Horizontal Partitioning: Distributing the rows of a table into several separate files.
Vertical Partitioning: Distributing the columns of a table into several separate physical records.
File organization: (sequential, indexed) A technique for physically arranging the records of a file on secondary storage devices.
Sequential file organization: The storage of records in a file in sequence according to a primary key value.
Indexed file organization: The storage of records either sequentially or nonsequentially with an index that allows software to locate individual records.
• Convert an ER diagram into a relational database design, clearly indicating all the primary keys, foreign keys, and relationship constraints (e.g., FK NameID in ORDER must exist as PK CustomerID in CUSTOMER.
• For an ER diagram, identify all Primary and foreign keys and cardinalities.
• Cardinality constraint: Specifies the number of instances of one entity that can or must be associated with each instance of another entity.

Chapter 7: SQL

Creating tables
Create table MINE (name TEXT (30));
Setting null values
Alter table
Alter Table MINE add NewName Integer NULL;
Drop table
Drop Table MINE
Distinct
Select distinct NewName
From MINE;
Insert, select, delete, update (where, from)
Select * from MINE where NewName is Null;
Expressions
Select AMT from ORDER where AMT >50.00;
And, or, not
Select from MINE where NewName = 7;
Alter table mine add midname TEXT (20);
In, not in
Select from MINE where midname in ('yourname', 'myname');

Chapter 9: Client Server

Terminologies, as usual
File server: A device that manages file operations and is shared by each of the client PCs attached to the LAN.
Fat client: A Client PC that is responsible for processing presentation logic, extensive application and business rules logic, and many database management system function.
Thin client: A PC configured for handling user interfaces and some application processing, usually with no or limited local data storage.
Partitioning: division of a logical database or its constituting elements into distinct independent parts. Database partitioning is normally done for manageability, performance or availability reasons.