• 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

Card Range To Study



Play button


Play button




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;

81 Cards in this Set

  • Front
  • Back

5 limitations of file system

- data redundancy

- lack of security

- lack of data integration: islands of information, hard to see the big picture

- data anomalies

- application dependent on data structure

- no concurrency control



Raw facts that don't have meanings unless processed into information


A character or a group of characters that is used to define and store data. Each attribute constitutes a fieldúhhj


A logically connected set of fields that describe a person, place or thing. Each customer would constitute a record


A collection of related records

Which characteristics of the file system can lead to data redundancy?

The file system are unable to manage relationships between data

What's data independence and why it's important?

Data access programs not affected when data characteristics change. Improves efficiency and reduces maintenance costs

Why database design is important

- Improves data integrity by reducing redundancies and anomalies

- good DBMS software doesn't overcome a bad design

A database houses which 2 things?

A collection of 1 or more tables and metadata

What's metadata?

Data about data. Including attribute characteristics and relationships between entity sets

What's the most important advantage of relational databases?

Provides structural and data independence and provide conceptual simplicity

Relational databases are good for designers because

They free designers from the complexities associated with physical data representation

Logical independence means

You can change the internal model without affecting the conceptual or external model (eg, changes to the DBMS)

External model

The end user's view of the database. Allows customizable views of the ERD based on the viewer

Conceptual model

The global view of the database through a ERD

Internal model

Representation of the database as seen by the DBMS.

Physical independence means

Can change physical model without affecting the internal model (eg, changes to the hardware)

Physical model

Lowest level of abstraction, describe the physical storage of data

Secondary key is used for

Data retrieval purposes, so don't necessarily yield a unique result but should also be restrictive enough that it produces manageable number of results

Associative vs transactional entities

Associative entities inherit their primary keys from their parent entities, while transactional have their own primary keys as well as foreign keys from patents.

What's a weak entity?

1. Existence-dependent on its parent entity

2. Inherits at least part of its primary key from the parent

What's a strong (identifying) relationship?

Between a weak entity and it's parent

Recursive relationship

Exists when an entity is related to itself. Eg a course is a prerequisite to another course

What's a weak (non-identifying) relationship?

When the participating entities have their own independent primary keys. Both identities are strong.

What's a data dictionary?

Stores data elements and their relationships

Define DBMS

A collection of programs that allows users to perform:

1. Data dictionary management

2. Data storage management

3. Data transformation and presentation

4. Security management

5. Multi user access control

6. Backup and recovery

7. Data integrity management


Data definition language. Used by DBMS to define database


Data manipulation language. Used to update, retrieve, or delete data. Eg, SQL language


Database application program. Interacts with the DBMS via SQL. Allows users to perform data entry, queries, maintenance...etc

Schema is

The logical view of the entire database as seen by the database administrator


The view as seen by the user or application programmer. Aka view

What are 3 advantage of different views depending on users

1. Security

2. Customizable appearance

3. Unchanging when DBMS, hardware or OS changed

What's concurrency control

Conflict when multiple users are making changes to same data. Controlled by row or cell locking

What are 5 disadvantages of DBMS

1. Size

2. Complexity

3. Costs: conversion, software, hardware

4. High impact of failure

5. Performance

Kernel entity/base relation

A table whose rows will be stored physically


A tuple of real data in a table

Compound attribute

Composed of logically grouped attributes such as inv-line and inv-id

Single valued attribute

Holds a single value for each occurrence of entity

Multi-valued attribute

Holds multiple possible values for each occurrence. Eg, student contacts

Derived attribute

Calculated from other attributes

Subsetting criteria

An attribute whose finite values divide entity instances into criteria. Eg, student/instructor

Constraints are implemented thru:

1. Data type: characters or numbers

2. Attribute domains

3. Default values

An intersection of row and column in a table contains a

Data value

Besides entities and relationships, what information do a conceptual schema contain?

1. Data security and integrity information

2. Constraints

3. Semantic information

Internal schema contains information about:

- storage space allocation for data/indexes

- data compression/encryption technique

- record descriptions with sizes for data storage

- record placement

Alternate keys are

Candidate keys that are not chosen as primary key

Secondary keys are

Any keys used for data retrieval

Compound/composite keys

Attributes that together can identify a single entity instance

5 desirable characteristics of primary key

1. Unique for each instance

2. Unchanging over time

3. Not be null

4. Auto-generated

5. Consistent

What's the difference between inner vs left/right outer join?

Inner join only joins fields with matching primary/foreign keys

Multiplicity of entity in a relationship consists of:


Ternary relationship

Involves 3 or more entities. Appears in conceptual level only

A M:N relationship is aka

Non-specific relationship

Transactional entities must have its own primary key as well as

Foreign keys of related entities


Bottom up process of identifying super type among several subtypes by finding common characteristics and relationships


Top down process of identifying subtypes for a given super type by finding unique characteristics and relationships

Redundant relationship

Can be deleted without losing any information generation ability of the model

Update anomalies

The same change has to be done many times due to the presence of redundant data

Insertion anomalies

One or more attributes can't be added without the presence of other attributes

Deletion anomalies

Delete 1 attribute resulting in loss of data about other attributes that we wish to keep


All key attributes are defined (no repeating groups), and when all remaining attributes are dependent on the primary key

A table in 1NF is automatically in 2NF when:

It's primary key composes of a single attribute

Advantages of denormalized table

-use less processing power to perform join between tables.

- maintain historical accuracy. Eg maintain a table for past salary based on old hourly rates

Partial dependency

B is partially dependent on A if it's also dependent on a subset of A. Eg EmpID + name determines Emp_address

Full dependency

Removal of any attributes from determinant result in not able to determine B. Eg Student _ID + course_ID determine grade

Self determination

A determine A


If B is a subset of A, then A determines B


If A determines B, then AC determine B


If A determines B and B determines C, then A determines C


If A determines BC, then A determines B and A determines C


The opposite of decomposition. If A determines B and A determines C, then A determines BC

Requirements statement

A description of the business processes that can be used to derive business rules

To find entities

Look for nouns in the requirements statement and beware of synonyms.

To find relationships

Look for verbs in the requirements statement. Determine multiplicity constraints and document and update data dictionary

To find attributes

Use the requirements statement to find nouns that indicate a property, quality, identifier or characteristic of the entity


The child can exist independently even if the parent dies: engine (child) and car. Denoted by an open diamond in the ERD


A stronger form of aggregation relationship in which the child dies when the parent dies. Eg book (parent) contains index (child). Denoted by a closed diamond in the ERD

Enhanced modelling concepts are


- aggregation

- composition

Processes of forming a conceptual design

Identify entities, relationships, and attributes

Determine relationship types

Determine attributes domain

Select keys

Consider enhanced modelling concepts

Check model for redundancy

Validate model against user transactions

Review model with user

Look for redundancy between relationship by

Look for possible synonyms between 1:1 relationship

Sometimes what can make relationship non-redundant

Time dimension