CP363

studied byStudied by 12 people
5.0(1)
Get a hint
Hint

Database System

1 / 192

flashcard set

Earn XP

Description and Tags

193 Terms

1

Database System

computerized record keeping system that consists of related data

New cards
2

Database System Components

Users, Hardware, Software

New cards
3

User Support

Supports single or many users

  • Data is Integrated - unify distinct files while eliminating redundancy

  • Data is shared - different users will have different access and views to the same data

New cards
4

Database Administrators

create the data base and incorporate technical controls to enforce decisions made by the DBA

New cards
5

Why Database - Data Dictionary

stores definition of data and relationships, changes are automatically recorded

New cards
6

Why Database - multiaccess user support

Shared Data between users, multiple users can access the database at the same time without compromising integrity

New cards
7

Why Database - Reduced redundancy and inconsistency

Inconsistent data happens when one redundant data has been updated and the other has not

New cards
8

Why database - Transaction Support

Several updates in one transaction guarantees all or none are updated

New cards
9

Why Database - Data integrity support

ensures that the data in the database is accurate, reduce redundancy, increase consistency

New cards
10

Why Database - security

only means of access to database is through proper channels, promotes data privacy

New cards
11

Why database - backup/recovery

recovery after possible failures

New cards
12

Data Independence

changing info in one place without changing it in another

  • Changes in application programs without changing the structure of the underlying data

  • Database can grow without impairing existing applications

New cards
13

Disadvantages of the DBMS

  • Increased Costs: hardware and software costs, hiring experts

  • Management Complexity: different types of technology is harder to manage

  • Vendor Dependence: reluctantly to change the system once it has been established

  • Frequent Update/Replacement Styles: costs incurred to update and train people

New cards
14

Conceptual Schema Design - Stage 1

Choice of model: User requirements and real world concepts should go into design

New cards
15

Conceptual Schema Design - Stage 2

Normalization: Reduces complexity and avoids redundancy by adjusting diagrams

New cards
16

Conceptual Schema Design - Stage 3

Optimization: Promotes good performance of the database

New cards
17

Entities

real world entity that has an independent existence, Represented by a table and attributes

New cards
18

Strong Entity

exist on thier own and do not rely on others

New cards
19

Weak Entity

existence depends on the existence of another strong entity

New cards
20

Relationships

associates entities with one another

New cards
21

Mapping Constraints - One-to-One

manager can manage only one department but each department can only have one manager

New cards
22

Mapping Constraints - One-to-Many

Department can have several employees but each employee can only work in one department

New cards
23

Mapping Constraints - Many-to-Many

A supplier can supply parts to several jobs a jobs can receive parts for several suppliers

New cards
24

Total Participation

Relationship is total - (indicated by double lines):

EX: every department must be managed by a manager

New cards
25

Partial Participation

Relationship is partial between two entities (indicated by a single line):

EX: not every employee manages a department

New cards
26

Simple/Atomic Attributes

Not divisible (ex. Part #, weight)

New cards
27

Composite Attributes

Consists of several simple attributes (ex. Address)

New cards
28

Single valued attributes

unique value, not shared among data entries (ex. SIN#)

New cards
29

Multi valued attributes

attributes that can be shared among data (ex. College majors, skills)

New cards
30

Stored Attribute

are stored in the database (ex. Date of birth)

New cards
31

Derived Attribute

can be derived from stored information (ex. Age)

New cards
32

Key Attribute

unique attribute that is distinct for each individual entity instance and can be used to identify an entity

New cards
33

Candidate Key

minimal subset attributes that uniquely identifies an entity (ex. Employee #)

  • Can be specified using the unique clause

New cards
34

Primary key

candidate key chosen by designer to access each entity

  • Only one primary key per table

  • Can be specified after the variable type or before with the values that make up the key

New cards
35

Null Values

Represents not applicable, unknown missing information or not known information

New cards
36

Database Schema

created in the database design phase to describe the database and not expected to change

New cards
37

Database Instance

data in the database at a particular moment in time

New cards
38

Levels of Database Architecture (ANSI/SPRAC) - Internal Level

shows how data is stored inside the system such as file organization and access paths

New cards
39

Levels of Database Architecture (ANSI/SPRAC) - Conceptual Level

deals with the modeling of the whole database, conceptual schema is defined

New cards
40

Levels of Database Architecture (ANSI/SPRAC) - External Level

models user oriented description of parts of the database, views for users

New cards
41

Logical Data Independence

(external/conceptual): ability to modify conceptual schema without changing external views and application programs

New cards
42

Physical Data Independence

(internal/conceptual): modify internal or physical schema without changing conceptual or view level schema or application programs

New cards
43

Data Definition Language (DDL)

  • For defining schemas at various levels

  • Used by database designers and administrators

New cards
44

Data Manipulation Language (DML)

  • Used to construct and use the database

  • Used by end users for insertion, deletion, updates, retrievals

New cards
45

Subclass

allows sub groupings of entities

  • Members of a subclass inherit all the attributes of the superclass

  • Each sub class has its own attributes along with inherited attributes

New cards
46

Superclass

main type of the sub grouping

New cards
47

Specialization

  • Process of defining a et of subclasses of an entity type

    • Based on some distinguishing characteristic of the entity type

    • Multiple specializations can be defined on a single entity type

New cards
48

Specialization - Disjointness Constraint

  • Disjoint: an entity can be a member of at most one of the subclasses of the specialization

  • Overlapping: the same entity can be a member of more than one subclass of the specialization

New cards
49

Specialization - Completeness Constraint

  • Total: every entity in superclass must be a member of some subclass

  • Partial: an entity might not belong to any of the subclasses in the specialization

New cards
50

Generalization

Result of taking the union of two of more lower level entity types to produce a higher level entity type

New cards
51

Aggregation

Abstraction through which relationships are treated as higher level entities

  • Create new higher level entity called assignment to treat as the new entity

New cards
52

Relational Model

  • Uses tables called relations to represent a collection of related data values

    • Rows are called tuples

    • Columns are called attributes

      • Number of attributes (# of columns) is called the degree/arity

New cards
53

Relational Model - Domain

data type describing the values that can appear in each column

  • Domain is a set of atomic values that are indivisible

New cards
54

Super Key

key attributes and other attributes of a relation

  • Allows you to be more specific to find a record

New cards
55

Relations

values that can be read but not updated by definition

New cards
56

Relvars

variables that can be read and updated by definition

New cards
57

Optimization

system component that determines how to implement user requests

New cards
58

Catalog

set of system relvars that contain descriptors regarding various objects that are of interest to the system itself

New cards
59

Transaction

logical unit of work involving several operations that begins by begin transaction and terminates normally or abnormally

New cards
60

Atomicity

Transactions are guaranteed either to execute or not execute at all

New cards
61

Durability

once a transaction successfully commits its updates are guaranteed to be applied to the database even if the system fails

New cards
62

Isolation

database updates by given transactions are kept hidden from all distinct transactions unless the first transactions are successfully committed

New cards
63

Serializability

interleaved execution of concurrent transaction is guaranteed to produce the same result as executing the same transactions in an unspecified order

New cards
64

Commit (normal Termination)

operation that signals the end of a successful transaction

New cards
65

Rollback (abnormal termination)

operation that signals the unsuccessful end of transaction

New cards
66

SQL

Structural Query Language

  • Non procedural Language - no loops, conditionals or functions

  • Combine with other programming languages for more functionality

New cards
67

SQL Data Types

  • Numeric

    • Integer and floating point numbers

    • Integer -  size to 40 digits, 4 bytes in DB2

    • Small int - smaller storage space, 2 bytes in DB2

    • Fractional Numbers - specify scale(default) and size or none

  • Character String

    • Fixed: CHAR(size) specified, reserved fixed amount in memory

    • VARCHAR(size), does not reserve max space

  • Date / Time

    • YYYY-MM-DD for dates

    • HH.MM.SS for times

New cards
68

Foreign Key

  • Combination of columns in one relationship that references primary key attributes of a second relationship

  • Uses the keyword references

New cards
69

Default Value

specifies a value to when no value is given

New cards
70

Check Conditions

  • Ensures that every row in the table satisfies a set condition

  • Condition can be any valid expression that evaluates to true or false

New cards
71

Referential Integrity Actions

  • Set Default - the attribute value is set to default value

  • Set Null - attribute value is set to null

  • Cascade - updates are propagated, each attribute value is updated

  • No Action

New cards
72

Alter Table

to update and modify an existing table by adding/update/delete an existing column

  • Add/Delete column by using ADD command

    • Add column at any time if not null is not specified

  • Modify column definition using MODIFY command

    • Can increase character column width, number of decimal place and number of digits at any time

New cards
73

Drop Table

  • Use DROP TABLE command at the bottom of the dependency chain to properly delete

  • Use CASCADE CONSTRAINTS to drop all constraints  that refer to professor table

New cards
74

INSERT

  • Insert - adds a new row to the selected table

    • Insert  by explicitly or implicitly specify  a query

  • Insert results of a query into a table

New cards
75

DELETE

removes a row or a set of rows from the selected table

\
__Basic Syntax:__

DELETE FROM table

WHERE <cond>
New cards
76

UPDATE

changes values of existing row in table

\
__Basic Syntax:__

UPDATE table

SET attribute1 = expression……

WHERE <cond>
New cards
77

Views

Virtual Table opposed to a base table - specific view of a database group for users

New cards
78

Characteristics of Views

  • View is not created at definition, instead materializes on the first use

  • Do not need to create a physical table --> derive table from implementation

  • View tables are updated automatically  if the base table info is updated

New cards
79

Purpose of Views

  • Performance optimization

    • Used when subqueries are repeated through existing queries

  • Security

    • Users only can be provided with data that is needed and not all of it

    • Provide read only access

New cards
80

Creating Views

knowt flashcard image
New cards
81

SELECT

  • Use * to list all the attributes

    • If Using * the WHERE clause is optional

  • Can pass strings and conditional statements through

New cards
82

ORDER BY

  • Order by Descending (DESC) or Ascending (ASC)

  • Ascending order is the default

  • Specify ordering with multiple different attributes

New cards
83

AS

  • Serves as an Alias to rename an attribute to be more specific

New cards
84

DISTINCT

  • Eliminates duplicate values

New cards
85

LOGICAL OPERATORS

  • AND, OR, NOT can be used to combine simple conditionals

  • Precedence: NOT --> AND --> OR

New cards
86

JOINING TABLES

  • Same attribute names in different tables

  • Use . To specify the attributes from the tables

New cards
87

EXISTS

  • Used to test he existence of any record  subquery

  • Returns true of conditions are met

New cards
88

UNION

  • Combines the result set of two or more select statements

  • Both tables must have the same number of columns and be similar types

New cards
89

MINUS/NOT IN

  • Not conditions for entries

  • Minus does not work for MySQL, you must use NOT IN

New cards
90

LIKE

  • Allows you to pull entries that are similar to a given condition

  • Can be used in place of an equals sign to a string

  • Case sensitive matching

    • % will match 0 or more characters

    • _ will match a single character

New cards
91

BETWEEN

  • Simple shorthand for a range restriction instead of using relational operators

New cards
92

Aggregate Functions

  • Take a multiset of values and return a single value

  • Average, Minimum, Maximum, Total/Sum and Count

New cards
93

GROUP BY

  • Group by a certain table column that have the same value

New cards
94

HAVING

  • List records that have a certain condition

  • Can be combined with conditionals to create constraints

  • Can be used with nested queries

New cards
95

Problems with Poor database design

  • Redundancy - duplicate data

  • Update anomalies - Direct consequence of redundancy

  • Insertion Anomalies - Cannot insert new data unless there is a record that applies to it

  • Deletion Anomalies - Lose important information from deletion

New cards
96

Decomposition

  • Split the relation into two or more relations

  • Use join to get back to the original information

New cards
97

1NF Normalization

  • A relation is said to be in 1NF if it contains only atomic, simple and scalar data

  • This data is usually comma delimited and contains multiple values associated with one record

New cards
98

Full Functional Dependency

  • FD between X -> Y is full if the removal of any attribute from X means that the dependency does not hold

New cards
99

Partial Dependency

FD between X -> Y is partial if there is some attribute AeX that can be removed from X and the dependency will still hold

New cards
100

2NF Normalization

A relational schema is in 2NF if it is at first in 1NF and every non key attribute is fully functionally dependent on the primary key / candidate key of the relation

New cards

Explore top notes

note Note
studied byStudied by 25 people
... ago
5.0(1)
note Note
studied byStudied by 25 people
... ago
5.0(1)
note Note
studied byStudied by 13 people
... ago
5.0(1)
note Note
studied byStudied by 1 person
... ago
5.0(1)
note Note
studied byStudied by 36926 people
... ago
4.9(188)
note Note
studied byStudied by 17 people
... ago
5.0(1)
note Note
studied byStudied by 64 people
... ago
5.0(2)

Explore top flashcards

flashcards Flashcard (23)
studied byStudied by 29 people
... ago
4.5(6)
flashcards Flashcard (66)
studied byStudied by 21 people
... ago
5.0(1)
flashcards Flashcard (103)
studied byStudied by 2 people
... ago
4.0(1)
flashcards Flashcard (20)
studied byStudied by 10 people
... ago
5.0(1)
flashcards Flashcard (319)
studied byStudied by 9 people
... ago
4.0(1)
flashcards Flashcard (208)
studied byStudied by 21 people
... ago
5.0(1)
flashcards Flashcard (28)
studied byStudied by 2 people
... ago
5.0(1)
flashcards Flashcard (102)
studied byStudied by 1 person
... ago
5.0(1)
robot