Computer 1630 Midterm

- 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

The file system are unable to manage relationships between data

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

- Improves data integrity by reducing redundancies and anomalies

- good DBMS software doesn't overcome a bad design

A collection of 1 or more tables and metadata

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

Provides structural and data independence and provide conceptual simplicity

They free designers from the complexities associated with physical data representation

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

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

The global view of the database through a ERD

Representation of the database as seen by the DBMS.

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

Lowest level of abstraction, describe the physical storage of data

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 entities inherit their primary keys from their parent entities, while transactional have their own primary keys as well as foreign keys from patents.

1. Existence-dependent on its parent entity

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

Between a weak entity and it's parent

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

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

Stores data elements and their relationships

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

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

1. Security

2. Customizable appearance

3. Unchanging when DBMS, hardware or OS changed

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

1. Size

2. Complexity

3. Costs: conversion, software, hardware

4. High impact of failure

5. Performance

A table whose rows will be stored physically

A tuple of real data in a table

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

Holds a single value for each occurrence of entity

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

Calculated from other attributes

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

1. Data type: characters or numbers

2. Attribute domains

3. Default values

Data value

1. Data security and integrity information

2. Constraints

3. Semantic information

- storage space allocation for data/indexes

- data compression/encryption technique

- record descriptions with sizes for data storage

- record placement

Candidate keys that are not chosen as primary key

Any keys used for data retrieval

Attributes that together can identify a single entity instance

1. Unique for each instance

2. Unchanging over time

3. Not be null

4. Auto-generated

5. Consistent

Inner join only joins fields with matching primary/foreign keys

Participation..cardinality

Involves 3 or more entities. Appears in conceptual level only

Non-specific relationship

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

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

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

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

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

It's primary key composes of a single attribute

-use less processing power to perform join between tables.

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

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

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

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

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

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

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

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

-generalization/specialization

- aggregation

- composition

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 possible synonyms between 1:1 relationship

Time dimension