Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

ISTE 608 > Midterm > Flashcards

Midterm Flashcards

1
Q

What are key differences between databases and file systems?

A

Data separation and isolation – file systems store in separate files, special programs must be written; databases store all data together and has algorithms to automatically relate and combine data

Data duplication – in files, data is often duplicated, difficult to update; in databases, there is minimal data duplications

Program/data independence – in files, if a file structure is updated, all programs must be updated. In databases , the format is stored as part of the db, and application programs only need to identify data needed, DBMS does access

File compatibility – files may have to be changed to compatible format, DMBS does all the data access not programs

Ability to Represent the User’s Perspective of Data – in files, separate files don’t store relationships, Databases store relationships

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Disadvantages to file systems

A

Application program dependency
Data duplication
Separated and isolated data
Long development times
Increased maintenance requirements

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Properties/Definition of a Relational Database

A

Shared, Self Describing, Organized Collection of Logically-Related Persistent Data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Possible types of data that can be stored in a database

A

Text
Numbers
Date/Time
Graphics and images
Sound and video
Programs and other objects

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What are the four components of a relational database?

A

Application metadata – data about the structure of forms, reports, and queries
Metadata - data about the structure of the DB
Index – an index aids in searching
User data – data tracked by users

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Database Management System (DBMS)

A

A DBMS is a data storage and retrieval system which permits data to be stored non- redundantly while making it appear to the user as if the data is well integrated.
To implement a database schema a Database Management System must be used.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What are the three DBMS subsystems and their purpose?

A

Design Tools subsystem = software that is used to create tables, forms, queries, and reports; provides interfaces for built-in programming languages.

Run-Time subsystem = processes the components created w/Design Tools; executes & displays data in forms, etc. & provides access for standard
programming languages (C++, Java, etc.).

DBMS engine = software that facilitates the transfer of data between the database and the design and run-time subsystems: handles requests to access the database tables, does transaction management, record locking, backup & recovery.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Disadvantages of databases

A

Database Administrators (DBAs) and other specialized personnel
needed

Installation and management costs

Conversion costs

Need for explicit backup and recovery

Political problems / organizational conflict

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Summary steps of database development lifecycle

A
  1. Create a Data Model from gathered requirements -Entity-Relationship Model
  2. Transpose Data Model into Relation(s)
  3. Normalize Relations
  4. Create the Relational Schema within Database Management System.
    (Metadata)
  5. Define forms, queries, reports, menus, if supported within DBMS or
    external application programs.
  6. Populate with User Data.
  7. Maintenance
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

DBMS independent vs dependent

A

independent -> HIGH-LEVEL or conceptual level (e.g., E-R diagram)

dependent -> LOW-LEVEL or physical level(e.g., file layouts/structures,
indexing, OS access strategies)
DBMS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Data Modeling

A

define the logical structure of a database and are the fundamental entities used to introduce abstraction in a DBMS. Data models define how data are connected to each other and how they are processed and stored inside
the system.

Used as a means of communication between database developer and client

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is an attribute?

A

data item that is used to describe an entity.
show by ovals and connected to the entity rectangle with a line

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Store vs derived attr

A

Stored – actually stored
Derived – the data value is calculated (dotted circle) age (calc by DOB)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is an entity class?

A

Something the user wants to track.
Typically nouns.
Represented by a rectangle with a singular name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is an entity instance?

A

The actual occurrence of the data
Usually never shown

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Simple vs Composite attributes

A

Simple – composed of one piece of data
composite – an attribute composed of other attributes (like an address)
Not shown in crow’s foot notation

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Single vs mutli-valued attr

A

Single – one single piece of value
Multi (double circle) – an attribute that stores multiple data values (skills = carpentry, plumbing, painting)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is identifier?

A

Most entities will have one
One or more attributes that identify an entity instance
If two or more, composite identifier

Can be unique or non-unique

Denoted by underline in Chen Notation

19
Q

What are the elements of ER Models?

A

Entity
Attribute
Relationship

20
Q

Chen vs Crow’s Feet

A

Crow’s foot notation and Chen notation are both used in entity-relationship diagrams to represent the relationships between entities in a database. The main difference between the two notations lies in the way they represent cardinality and optionality in relationships. In Crow’s foot notation, cardinality and optionality are represented using different symbols such as crow’s feet and dashes. In Chen notation, cardinality and optionality are represented using the use of different line styles and symbols. Both notations serve the same purpose of visually representing the structure of a database, but they differ in their visual representation of cardinality and optionality in relationships.

21
Q

What are the three types of database relationships?

A

One-to-one (rare)
one-to-many
many-to-many

22
Q

What is identifier?

A

Most entities will have one
One or more attributes that identify an entity instance
If two or more, composite identifier

Can be unique or non-unique

Denoted by underline in Chen Notation, Crow’s -> appears in the identifier area

23
Q

Composite identifier

A

If an identifier contains two or more attributes it is called a composite identifier

24
Q

Steps to creating a database

A
  1. Create a Data Model from gathered requirements.
  2. Transpose Data Model into Relation(s).
  3. Normalize Relations.
  4. Implement the relational schema within a Database Management System (DBMS). (Metadata)
  5. Define forms, queries, reports, menus, etc. (Application Metadata), if supported within DBMS or external application programs.
  6. Populate database with user data.
  7. Maintenance
    ¡ Change happens … expect it … plan for it as best you can!
25
Q

Transposing Process

A
  1. The name of the entity becomes the name of the “relation”
  2. Attributes of the entity become attributes of the “relation”
    (Except for any composite attributes (shows a logical connection) and derived attributes (not
    physically stored))
    Relational Schema Notation (one-dimensional view)
    § RELATION(attr1, attr2, attr3, …)
    § Example: A(2, 3, 4, 5)
  3. The identifier of the entity becomes the “proposed” primary key of the “relation”, denoted by
    underlining
26
Q

Relation

A

A two-dimensional structure that holds data pertaining to an object of interest.

Columns are attributes, rows are tuples of information or an entity instance.

27
Q

Natural vs Surrogate Primary Key

A

A Surrogate primary key exists only to create a unique identifier.

28
Q

What is a primary key?

A

A column, or group of columns, used to identify a row.

Column values, when grouped must be unique
May not contain null
must be minimal

29
Q

Two categories of SQL statements and what statements fall under that category

A

Data Definition Language (DDL)
Deals primarily with metadata
CREATE…
DROP…
ALTER

Data Manipulation Language (DML)
Deals primarily with user data
INSERT
SELECT
UPDATE
DELETE

30
Q

SELECT Statement structure

A

Shows user data in a table

SELECT * | attributes_to_include
FROM tableName;

31
Q

NULL Values

A

Means unknown or nothing
§ Does NOT mean an empty string(‘’) or a string of a space (‘ ‘)

32
Q

What is normalization, and what part of the development process is normalization?

A

Representation level.

Normalization: decomposing relations to avoid anomalies when inserting, updating or deleting data.
Also serves to reduce redundancy of the data

33
Q

Functional Dependency

A

Used to show a relationship between attributes within a relation
Functional Dependency: If an attribute, (A), determines the value of another attribute, (B), then A functionally determines the value of B.
Notation: A->B

Therefore if we know the value of A, the value of B can be determined

34
Q

Candidate Key

A

A candidate key functionally determines all other attributes in the relation

Since each tuple in a relation must be unique, there is a unique candidate key for each
tuple

A relation could have several possible unique candidate keys

Each determinant (simple or composite) that is eligible to serve in the role of primary key
are called candidate keys. You decide to use one of them as the primary key

35
Q

How to determine primary key

A

The primary key is an attribute, or group of attributes, that will uniquely identify a tuple from all other tuples in a relation

The primary key functionally determines all other attributes in the relation

There can only be one primary key for a relation
That primary key can be composed of more than one attribute (Composite Primary Key)

Before a “proposed primary key” (Step #2) can be confirmed, any candidate keys must be
determined through an examination of functional dependencies for the relation (Step 3.B)

If there is only one candidate key, it will become the primary key
 If there are multiple candidate keys, then one must be selected to become the primary key
 Sometimes one candidate key will be “better” than the others, other times, candidates may be “equivalent”
 A primary key is AWALYS a candidate key. A candidate key may, or may not, become a
primary key.

36
Q

What is a database anomaly?

A

A database anomaly is a fault in a database that can occur when data is stored in a flat database or due to poor planning. Anomalies can occur when data is stored multiple times unnecessarily, or when all data is stored in a single table

37
Q

Three types of database anomalies

A

Insertion anomaly
When it is not possible to insert new data into a table due to missing information. For example, if you try to insert a supplier who has no order.

Deletion anomaly
When deleting data unintentionally results in the loss of other important data. For example, if you delete the last single order of a supplier, the data of the supplier will also be lost.

Update anomaly
When updating a single data value requires multiple rows to be modified. For example, to update a supplier, you need to update in each and every purchase order of the supplier.

38
Q

What is a determinant?

A

Determinant – the attribute(s) on the left side of a functional dependency

39
Q

What are composite keys?

A

Composite Candidate Key: A candidate key made up of
more than one attribute.

Composite Primary Key: A primary key made up of more
than one attribute.

40
Q

Second Normal Form

A

A relation is in second normal form if it is in first normal form, and
all of its non-key attributes are dependent on all of the primary
key.

Violation: partial dependency – when you have a non-key attribute that is dependent on only part of a composite pk (primary key).

41
Q

What is a foreign key, and how do you identify it and a reference statement?

A

Establishes a connection, or relationship, between relations.

When a primary key of one relation appears in any other relation, it is a foreign key.

Denoted by italics (when typing) or dashed underline (when handwriting)

CONTACT_INFO(contactID) must exist in CONTACT(contactID)

42
Q

What is referential integrity?

A

A reference statement enforces referential integrity.

Referential Integrity – the set of values that can be used as values for a foreign key value MUST be in the set of values from the respective primary key.

Example: CONTACT_INFO(contactID) must exist in CONTACT(contactID

43
Q

Third Normal Form

A

A relation is in third normal form if it is in 2NF and is free from
transitive dependencies.

Violation: The 2nd half of a Transitive Dependency: a non-key
attribute is functionally dependent on another non-key attribute

A -> B -> C

where A is the primary key, and B and C are not.

44
Q

BCNF

A

A relation is in BCNF if, and only if, all determinants are a candidate key (which would include primary key).

Determinant: the attribute(s) on the left side of a functional dependency.

Candidate Key: attribute(s) that could serve as a primary key, but for some reason was not selected to serve as the primary key

ISTE 608 (21 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions