• Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

Card Range To Study

through

image

Play button

image

Play button

image

Progress

1/25

Click to flip

Use LEFT and RIGHT arrow keys to navigate between flashcards;

Use UP and DOWN arrow keys to flip the card;

H to show hint;

A reads text to speech;

25 Cards in this Set

  • Front
  • Back

% Percent in SQL

LIKE uses patterns that are described using two specialcharacters: percent ( % ). The % character matches any substring.

Expressing cardinality with a line

We express cardinality constraints by drawingeither a directed line (->), signifying “one,” oran undirected line (—), signifying “many,”between the relationship and the entity.

Representing Relationships.One to One

For one-to-one relationships, a foreign key canbe added to either of the tables

Representing Relationships.One to many, many to one

Many-to-one and one-to-many relationships can be represented byadding a foreign key to the “many” side, containing the primary key ofthe “one” side

Representing Relationships.Many to many

Many-to-many relationships are represented as a newtable adding foreign keys for the primary keys of thetwo participating entities




and any descriptive attributes of the relationship Example: one student can have multiple advisors, and oneinstructor can advise multiple students




A good practice for many-to-many relationships is tosplit it in 2 one to many relationships

Basic operations and what do they do

Select Project Union Difference Cartesian product Intersection Join

Drawbacks of using file systems to store data

 Data redundancy and inconsistency


 Multiple file formats, duplication of information in differentfiles


 Difficulty in accessing data


 Need to write a new program to carry out each new task


 Data isolation


 Multiple files and formats


 Integrity problems


 Integrity constraints (e.g., account balance > 0) become“buried” in program code rather than being statedexplicitly Hard to add new constraints or change existing ones

Drawbacks of using file systems tostore data (Cont.)

Atomicity of updates


Failures may leave database in an inconsistent state with partialupdates carried out


Concurrent access by multiple users


Uncontrolled concurrent accesses can lead to inconsistencies


Security problems Hard to provide user access to some, but not all, data

Levels of Abstraction

Physical level: describes how a record (e.g., instructor)is stored.




Logical level: describes data stored in database, andthe relationships among the data.type instructor = recordID : string;name : string;dept_name : string;salary : integer;end;




View level: application programs hide details of datatypes. Views can also hide information (such as anemployee’s salary) for security purposes.

Instances and schemas

Physical schema– the overall physical structure of the database




Logical Schema – the overall logical structure of the database Example: The database consists of information about a set of customersand accounts in a bank and the relationship between them




Instance – the actual content of the database at a particularpoint in time (e.g. customer 1 with the account #1, amount…)




Physical Data Independence – the ability to modify thephysical schema without changing the logical schema

SQL Languages

Schemas are defined using a Data Definition Language (DDL); Data is modified/queried using a Data Manipulation Language (DML)

Data Models

A collection of tools for describing


 Data Data relationships Data semantics Data constraints




 Relational model


 uses a collection of tables (known as relations) to represent bothdata and the relationships among those data




 Entity-Relationship data model (mainly for databasedesign) uses a collection of basic objects, called entities, andrelationships among these objects.




 Others Semistructured data model (XML, RDF) permits the specification of data where individual data items of thesame type may have different sets of attributes.

DDL

DDL compiler generates a set of table templates stored in a data dictionary Data dictionary contains metadata (i.e., data about data) Database schema Integrity constraints Authorization

DML

Also known as query language

Design approaches

ER Model and Normalization theory

Storage manager

Storage manager is a program module thatprovides the interface between the low-leveldata stored in the database and the applicationprograms and queries submitted to the system.

Database Design.Design Phases

1. The initial phase of database design is tocharacterize fully the data needs of theprospective database users.


2. Next, the designer chooses a data model andtranslates these requirements into a conceptualschema of the database.


3. Review the schema to ensure it meets thefunctional requirements of the enterprise


4. logical-design phase


Map conceptual schema (E-R) onto theimplementation data model (Relational Model,i.e. tables)


5. physical-design phase Specify the the physical features of thedatabase



Transaction-management component

Transaction-management component ensures thatthe database remains in a consistent (correct) statedespite system failures (e.g., power failures andoperating system crashes) and transaction failures.

Concurrency-control manager

Concurrency-control manager controls theinteraction among the concurrent transactions, toensure the consistency of the database.

Database Architecture

Centralized Client-server Parallel (multi-processor) Distributed

dataindependence.

Complex computations (queries, views) are keptseparate from the raw data (tables)

domain of the attribute

The set of allowed values for each attribute

Attribute requirement

Attribute values are (normally) required to be atomic;that is, indivisible

Keys

Super key - unique


Candidate key - minimal


Primary key - chosen candidate key

referential integrity constraint

requires that the values in the referencing relationcan contain either a null value, or a primary key or a unique constraint of thereferenced relation