INLS 523 Midterm

Cardinality

1:N, 1:1, N:1

Sharing

A database allows multiple users and programs to access the database simultaneously.

what are the keywords for DDL?

CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, TRUNICATE

Who are the actors on the scene?

DBAs, Database Designers, End Users and System Analysts

Who are the workers that are behind the scene?

DBMS system designers and implementers, tool developers, operators and maintenance

DDL

Data Definition Language

DML

Data Manipulation Language

What are the 7 steps of ER-Relation Model?

Mapping of Regular Entity types, mapping weak entities, mapping binary 1:1 relations, mapping binary 1:N relations, mapping of binary M:N relations, mapping multivalued attributes, mapping N-ary relationships

Business Rules

More complex rules you can enforce in the application layer (password must be 15 characters )

does a foreign key need to be a primary key?

No

Entity integrity constraint

No primary key value can be NULL

attributes

Properties that describe your entities. Attributes contain values

Explicit Constraints

Rules you enforce in the database (birthdays can't be null)

What are the keywords for DML?

SELECT, FROM,WHERE (AND, OR, NOT)

SDL

Storage Definition Language

Key attributes

The attributes that form a primary key

Primary Key

The candidate key you select to be the main key in your table

database system

The database and the DBMS software together

Constructing

The process of strong the data on some storage medium that is controlled by the DBMS

Manipulating

This includes functions such as querying the database to retrieve specific data, updating the database to reflect changes in the miniworld and generating reports from data.

Defining

This involves specifying the data types, structures, and constraints of the data that needs to be stored.

how do you translate a scenario into UML design?

UML design draw schema (boxes and arrows)

VDL

View Definition Language

DBMS (Database Management System)

a collection of programs that enables users to create and maintain a database.

Database

a collection of related data.

DBA (Database Administrator)

a person responsible for authorizing access to the database, coordinating and monitoring its use, and acquiring software and hardware resources as needed.

UML

a standardized modeling language enabling developers to specify, visualize, construct and document artifacts of a software system.(Unified Modeling language)

Three-Tier Client-Server Architecture

adds an intermediate layer between the client and the database server called the application server or the web server

Foreign Keys

an attribute that references the primary key of another relation

persistent object

an object that has been assigned a storage location in a federated database

Candidate Key

any attribute that has the potential to be a key for a relation

Atomic Value

cannot be further broken down for an attribute. ex. First_Name = Tom (can't be further simplified )

Two-Tier Client-Server Architecture

components are distributed over two systems: client and server.

Deductive Database System

database systems that provide capabilities for defining deduction rules for inferencing new information from the stored database facts

DBMS system designers and implementers

design and implement the DBMS modules and interfaces as a software package

Tool developers

design and implement tools—the software packages that facilitate database modeling and design, database system design, and improved performance.

entity

domain/ category of things,

Canned Transactions

end user's constant querying and updating the database, using standard types of queries and updates

total participation

every provider must work in at least one clinic. Implies that providers can't exist in our database without an associated clinic.

conceptual

hides the physical storage structures but focus on describing entities, data types, relationships, user operations and constraints

referential integrity constraint

if a tuple in one relation A refers to a tuple in another relation B. B has to exist

Sophisticated end users

include engineers, scientists, business analysts, and others who thoroughly familiarize themselves with the facilities of the DBMS in order to implement their own applications to meet their complex requirements.

What are the parts of the three-schema architecture ?

internal, conceptual, external

Data

known facts that can be recorded and that have implicit meaning

external

level monitors what to hide and view specific to the end users

Standalone users

maintain personal databases by using ready-made program packages that provide easy-to-use menu-based or graphics-based interfaces. An example is the user of a tax package that stores a variety of personal financial data for tax purposes.

Naive or parametric end users

make up a sizable portion of database end users. Their main job function revolves around constantly querying and updating the database, using standard types of queries and updates—called canned transactions

Are multivalued attributes allowed?

no

does a relation have an inherent order?

no

partial participation

not every patient will have lab results but some will

Casual end users

occasionally access the database, but they may need different information each time. They use a sophisticated database query language to specify their requests and are typically middle- or high-level managers or other occasional browsers.

relationships

one entity references or is referenced by another entity

Specialization

process of classifying a class of objects into more specialized subclasses. Conceptual refinement and a IS-A subclass

System Analysts

programmers, developers and engineers

when can you need a self join?

references the same table. ex. when some attributes have similar values and you want to see which ones.

6 phases of design and implementation

requirements collection and analysis --> conceptual design --> choice of DBMS --> logical design(Data model mapping) --> physical design ---> system implementation and tuning

transaction-processing application

reservation systems or banking databases which make it possible by the concurrency control and recovery subsystems of a DBMS

Database Designers

responsible for identifying the data to be stored in the database and for choosing appropriate structures to represent and store this data.

Operators and maintenance personnel

responsible for the actual running and maintenance of the hardware and software environment for the database system.

user view

schema that describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group

internal

schema uses a physical data model and describes the complete detail of a storage and access paths for the database.

How do you identify key attributes, and multivalued attributes in an ER diagram? pg 233

should be atomic

catalog

similar to a data dictionary utility that includes a wider variety of information and is accessed mainly by users rather than by the DBMS software.

Cartesian Product

specifies all possible combinations of values from the underlying domains(CROSS JOIN)

participation

specifies whether an entity's existence depends on its being related to another entity

3 data types

string, numeric, date

Implicit Constraints

the basic rules of relational database. e.g. you can't have two identical tuples in the same relation

meta-data

the database definition or descriptive information is also stored by the DBMS in the form of a database catalog or dictionary

Generalization

the inverse process of generalizing several classes into a higher level abstract class that includes the objects in all these classes. conceptual synthesis, BELONGS TO superclass

end user

the people whose jobs require access to the database for querying, updating, and generating reports; the database primarily exists for their use

program-data independence

the property where the structure of data files is stored in the DBMS catalog separately from the access programs.

sorting adds order to relations

yes

multivalued attribute

An attribute that may take on more than one value for a given entity (or relationship) instance. ex. phone number

INSERT syntax

INSERT INTO table_nameVALUES (value1, value2, value3, ...);

What are the four main types of actions in database design?

Defining, Constructing, Manipulating, Sharing

What are the phases of database design ?

Design of a new application for an existing database or design of a brand new database starts off with a phase called requirements specification and analysis. These requirements are documented in detail and transformed into a conceptual design that can be represented and manipulated using some computerized tools so that it can be easily maintained, modified, and transformed into a database implementation. The design is then translated to a logical design that can be expressed in a data model implemented in a commercial DBMS. (In this book we will emphasize a data model known as the Relational Data Model from Chapter 3 onward. This is currently the most popular approach for designing and implementing databases using relational DBMSs.) The final stage is physical design, during which further specifications are provided for storing and accessing the database.

man who invented relational database

Edgar Frank Codd

Weak Entity

Entity types that do not have key attributes of their own