Term
| What is a database & what are it's 3 characteristics? |
|
Definition
- Database is a collection of related tables and queries that share rows and/or columns
-Persistent, Interrelated, & Shared |
|
|
Term
| Distinguish between a database and a database management system |
|
Definition
-Database: a collection of persistent data that can be shared and interrelated
-DBMS: collection on components that support data acquisition, dissemination, maintenance, retrieval, and formatting |
|
|
Term
| Concept of relational database, and how we relate DB's together |
|
Definition
| through the use of common key fields that link entities together. |
|
|
Term
|
Definition
Concept Data Modeling
LogisticalDatabase Design
Distributed Database Design
Physical Database design |
|
|
Term
| conceptual database design |
|
Definition
| uses data requirements and produces entity relationship diagrams (ERD's) for the conceptual schema and each external schema |
|
|
Term
|
Definition
| transforms the conceptual data model into a format understandable by a commercial DBMS |
|
|
Term
| Distributed Database design |
|
Definition
| allocate subsets of database to different sites, replicate subsets of database to improve availability |
|
|
Term
|
Definition
| actual programming of the DB takes place |
|
|
Term
|
Definition
1) Entity: has primary keys
2) Referential: has foreign keys to relate to another table |
|
|
Term
| role of Database Administrator & responsibilities |
|
Definition
-technical: update software, primary responsibility is database design, designing conceptual and internal schema, monitoring performance, selecting and evaluating software, managing security for DB usage, troubleshooting
-Non Technical: promote use of database techniques, setting standards, devising training materials, promoting benefits, consulting with users, planning new databases |
|
|
Term
| Database Manipulation Statements |
|
Definition
|
|
Term
|
Definition
| minimum # of times an entity will participate and maximum # of times an entity will participate |
|
|
Term
|
Definition
Supre Type
Sub Type
Inheritance
Completeness
Disjointness |
|
|
Term
|
Definition
a parent entity type in a generalization hierarchy
*represents a more general entity type than sub type |
|
|
Term
|
Definition
child entity in generalization hierarchy
* represents more specialized entity |
|
|
Term
|
Definition
| a data modeling feature that supports sharing of attributes between a super types and a sub type. subtype inherits attributes from super type |
|
|
Term
|
Definition
| every entity of a super type must be an entity in one of the sub types |
|
|
Term
|
Definition
| sub types do not have entities in common |
|
|
Term
|
Definition
1) Primary Key Rule
2) Naming Rule
3) Cardinality Rule
4) Entity Participation Rule
5) Generalization Hierarchy Participation Rule |
|
|
Term
|
Definition
| all entity types, relationships, and attributes have a name |
|
|
Term
|
Definition
| specified in both directions for each relationship |
|
|
Term
| Entity Participation Rule |
|
Definition
| all entity types participate in at least one relationship except for those in generalization hierarchy |
|
|
Term
| Generalization Hierarchy Participation Rule |
|
Definition
| at least one entity type in a generalization hierarchy participates in a relationship |
|
|
Term
|
Definition
1) entity name rule
2) attribute name rule
3) inherited attribute rule |
|
|
Term
|
Definition
| entity type names must be unique |
|
|
Term
|
Definition
| all attribute names must be unique within each entity type and relationship |
|
|
Term
|
Definition
| attribute names in a subtype do not match inherited attribute names |
|
|
Term
SQL
retrieving data from on table arranged in Order using SELECT |
|
Definition
SELECT FacFirstName, FacLastName, FacSalary
FROM Faculty
(Project Statement)
FROM FACULTY
Where FacSSN = '123456789' |
|
|
Term
SQL
Retrieving data from 2 tables (JOIN) |
|
Definition
SELECT OfferNo, CourseNo, FacFirstName, FacLAstName
FROM Offering INNER JOIN Faculty
ON Faculty.FacNo = Offering.FacNo
WHERE Off Term = 'Fall' AND OffYear = '2009'
AND FacRank = 'ASST' AND CourseNo LIKE 'IS*' |
|
|
Term
SQL
Retrieving data from 2 tables using aggregate functions (GROUP BY and HAVING) |
|
Definition
SELECT StdMajor, AVG(StdGPA) AS AvgGpa
FROM Student
WHERE StdClass IN ('JR','SR')
GROUP BY Std Major
HAVING AVG(StdGPA) > 3.1 |
|
|
Term
1-1 relationship
Which side does the FK go on? |
|
Definition
either; FK's are requires;
BOTH sides have 2 little lines across the long line |
|
|
Term
|
Definition
| FK goes on the Many side' 2 little lines on 1 and crows feet on many |
|
|
Term
|
Definition
| two 1-M relationships with an associates table |
|
|
