COSC 3380 - Midterm

4 types of relations

1:1, 1:n, n:1, n:m

Schema Construct

A component of the schema or an object within the schema, e.g., STUDENT, COURSE.

Relations

A relational database is a collection of relations which are frequently called tables (but also relations) can also be used to refer to elements of a table

Data Model

A set of concepts to describe the structure of a database, the operations for manipulating these structures, and certain constraints that the database should obey.

data model

A set of concepts to describe the strucutre of a database, the operation for manipulating these structures, and certain constraints that the database should obey

alias keyword

AS

application/semantic constraint

Cannot be directly expressed in schema Expressed and enforced by application program Example: Age needs to be less than 100

4 types of Integrity Constraints also referred to as schema-based or explicit constraints

Domain constraint - a valid set of values for an attribute (age must be an integer) Entity integrity constraint - primary key can't be null Referential integrity constraint - specified between two tables Key constraint - primary key cannot be duplicated, must be unique

Virtual Relation

Dynamically created to show user a custom view Generates virtual table, doesn't get stored Gets created each time you click on table Pulls data from various base tables

*Select* Pnumber, Dnum, Lname, Address, Bdate *From* PROJECT, DEPARTMENT, EMPLOYEE *Where* Dnum=Dnumber AND Mgr_ssn=Ssn AND Plocation="Stafford";

For everyone project located in "Stafford", list the Project number, the controlling Department number, and the department manager's Last name, Address and Birth date

superkey example

Imagine a table with the fields <Name>, <Age>, <SSN> and <Phone Extension>. This table has many possible superkeys. <SSN> <Phone Extension, Name> <SSN, Name> Of those listed, only <SSN> is a primary key, as the others contain information not necessary to uniquely identify records.

initial database state

database state when first loaded in

state constraints

define the constraints that a valid state of the database must satisfy

database designer responsibility

define the content, structure, constraints and functions or transactions against the database, works with end user and understand their needs

transition constraint

define to deal with state changes in the database, are enforced by the applications Example: Salary of an employee can not decrease

ERD: attributes

displayed in oval

invalid state

does not obey all integrity constraints

ERD: multivalued attributes

double oval

simple

each entity has a single atomic value

ERD

entity relation diagram

database state changes

everytime the database is updated

state is called

extension

referential integrity

foreign key must have a value that is already present as a primary key

conceptual schema

high level, semantic used to describe the structure and constraints for the whole database for a community of users uses conceptual or implementation data model

Inherent or Implicit Constraints

inherent constraints in data model Examples: 1 - relational model does not allow a list as a value for any attribute 2 - a relation cannot have duplicate tuples

3 commands used to modify the database

insert update delete

schema is called

intension

Active data dictionary

is accessed by DBMS software and users

Passive data dictionary

is accessed by users only

semantic integrity constraint

limitation on value (max value for a number)May have to be specified and enforced on a relational database Use triggers and assertions

physical schema

low level, internet, how the data is stored

composite

made of several components

transactions

may read some data, update certain values, or generate new data

multi valued

multiple values for that attribute

keyword constraint

name a constraint, useful for modifications later on

catalog

named collection of schema in an sql enviroment

specifying constraint

not nulll, default

key

not redundant, unique attribute

canned transactions

previously well defined functions

key constraint (primary key)

primary key cannot be duplicated no two tuples/records/rows can have the same combination of values for all their attributes

entity integrity (primary key)

primary key cannot be null this is referred to as a NOT NULL entity integrity constraint

attribute

properties of entities

self-referencing relationship

recursive relationship

Base relation

relation and its tuples are actually created and stored as a file by the dbms

implementation data model

relation data model, the concept between the conceptual and physical model

current relation state

relation state at a given time

constraints

restriction on the actual values in a database state derived from the rules of the mini-world that the database represents

valid state

satisfies all constraints

entity

specific thing or object

superkey

specifies a uniqueness constraint can have redundant attributes made up of multiple attributes every relation has at least one default superkey A superkey is a combination of attributes that can be uniquely used to identify a database record. A table might have many superkeys. Primary keys are a special subset of superkeys that do not have any extraneous information in them. all keys are superkeys, not the other way around

primary key clause

specifies one or more attributes that make up the primary key of a relation

unique clause

specify alterative keys

catalog

stores the description of a particular database

database state

the actual data stored in the database at a particular moment in time *also called the database instance/occurrence/snapshot* the term *instance* is also applied to individual database components like record instance, table instance, entity instance, etc. changes every time the database is updated AKA extension

logical data independence

the capacity to change the conceptual schema without having to change the external schema and their associated application programs Example: Adding data items, change constraints

physical data independence

the capacity to change the internal schema without having to change the conceptual schema Example: Reorganizing file structures, creating new indexes to improve database performance

database schema

the description of the database DOES NOT change frequently AKA intension

primary key

the main key designated to uniquely point to a row

candidate key(s)

the primary key is chosen from candidate keys, as a relational schema may have more than one key all the "candidates" within a table that could be used as a primary key, includes the actual primary key as well

internal schema

to describe the physical storage structure and access paths like indexes Uses a physical data model

cardinality

total number of values in domain

ERD: Keys

underlined

Key is identified in erd by

underlined

Data Definition Language (DDL)

used by the DBA to specify the conceptual schema of a database In many DBMSs, the DDL is also used to define internal and external schemas (views). In some DBMSs, separate storage definition language (SDL) and view definition language (VDL) are used to define internal and external schemas.

queries

used to access different parts of data

Data Manipulation Language (DML)

used to specify database retrievals and updates can be embedded into other programming languages like C++ can also be stand alone - referred to as query language

Database schema changes ____

very infrequently

SQL stands for

Structured query language

Meanings of NULL

Unknown value Unavailable or withheld value Not applicable attribute

Update command

Used to modify attribute values of one or more selected tuples • A WHERE-clause selects the tuples to be modified • An additional SET-clause specifies the attributes to be modified and their new values • Each command modifies tuples in the same relation • Referential integrity specified as part of Schema specification is enforced

entity set

a collection of entities stored in the database

database constructs

a component of the schema or an object within the schema Constructs are used to define the database structure Constructs typically include elements (and their data types) as well as groups of elements (e.g. entity, record, table), and relationships among such groups

data abstraction

a data model used to hide storage details and present the users with a conceptual view of the database

key to superkey relation

a key is a super key, but a superkey may not be a key

artificial/surrogate key

a number given to an entry to provide a key

valid state

a state that satisfies the structure and constraint of the database

weak entities are identified by

a(n) partial key and some attributes

functional dependency constraint

an attribute is a function of another attribute (e.g. y=4x+2)

weak entity

an entity without a unique key

schema diagram

an illustrative display of a database schema

database administrator responsibility

authorizing access to the database, coordinating and monitoring database use

referential integrity constraint

between two relations maintain consistency among tuples a tuple in one relation must refer to an existing tuple in that relation (cannot reference non-existent relations)

minimal superkey

cannot remove any attributes and still have the uniqueness constraint hold required for a key, but optional for a super key

check clause

check to make sure a value is something (e.g. number is greater than zero)

problems with foreign keys

circular reference or nonexistent reference

Self-Describing Data Models

combine the description of data with the data values

Centralized DBMS

combines everything into a single system including DBMS software, hardware, applications programs, and user interface processing

domain constraint

constraint on data type Examples: Numerical data types for integers and real numbers Characters Boolean Fixed-length strings Variable-length strings Date, time, timestamp Money

when not to use a dbms

cost is too high, when one is not needed, when it is infeasible, when no dbms may suffice

SQL command: Create

created a speicifed db, table, or index

virtual relaions

created through the create view statement

Two main types of constraints

Integrity/Schema Based/Explicit Application/Semantic

entity integrity constraint

No primary key value can be NULL

*Select* Bdate, Address *From* EMPLOYEE *Where* Fname = 'John' AND Minit = 'B" AND Lname = 'Smith'';

Retrieve the Birth date and Address of the employee(s) whose name is John B Smith

*Select* Fname, Lname, Address *From* EMPLOYEE *Where* Dname='Research' AND Dnumber=Dno;

Retrieve the First name, Last name, and Address of the employees who work at the research department

basic form of select statement

SELECT <attr list>\nFROM <table list>\nWHERE <condition>;

key constraint (super key)

SSN is a key of STUDENT • Any set of attributes including SSN is a Superkey • {SSN, Name, Age} • That superkey is not a key of STUDENT. • Any superkey formed from a single attribute is also a key. • A key with multiple attributes must have all its attributes together for uniqueness

external schema

The specific representation of an external view; the end user's view of the data environment Uses conceptual or implementation data model

Insert command

Typically inserts a tuple/row into a relation/table Constraints must map to values Attributes with Null or Default values can be left out