Brainscape's Knowledge GenomeTM

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


See full index

CS6400 > Exam 1 > Flashcards

Exam 1 Flashcards

1
Q

Functions of a Database Management System (DBMS)

A

Enables users to create and maintain a database. Allows for:

  • Defining: specifying data types, structures and constraints
  • Constructing: storing the data on some storage medium controlled by the DBMS
  • Manipulating: querying the database to retrieve data, updating to reflect changes in the miniworld, generating reports
  • Sharing:allows multiple users or programs access
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Query vs. transaction

A

Query causes data to be retrieved

Transaction causes data to be read or written

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

Other important functions of a DBMS

A

Protection: System and security protection

Maintenance: Allow the system to evolve as requirements change

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

Database System

A

Database + DBMS

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

Database Structure

A

File = Collection of records

Data records = Collection of data elements

Data elements = the different pieces of data

Data types = the specific type of each piece

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

Database Approach vs. File-Processing Approach

A

File-processing refers to old practice of each user or application having its own data.

Database approach is better because:

  • Self-describing nature of a database system
  • Insulation between programs and data, and data abstraction (i.e. program-data independence)
  • Support of multiple views of the data
  • Sharing of data and multiuser transaction processing
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Meta-data

A

The complete definition of database structure and constraints.

Structure of each file, the type and storage format for each data item, various constraints on the data.

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

Self-describing data

A

Data stored as item names and values in one structure. Used in NOSQL

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

Program-Data Independence

A

The structure of the data files is stored in the DBMS catalog, separately from the access programs (so a change to the data structure doesn’t break the access programs functioning)

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

Program-Operation Independence

A

An operation = method or function

An “interface” to an operation = the name of the method and parameters it takes.

The “implementation” of the operation = The part that takes action. Because the operation can change like a black box, you can change the workings but use the same interface. This is basically data-abstraction.

Data Abstraction allows Program-Operation Independence.

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

What allows program-data independence and program-operation independence?

A

Data Abstraction

Conceptual Representation (i.e. how the user might imagine the data or operations to work) ignores the implementation details required to actually perform operations.

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

Data Model

A

The conceptual representation of the data that hides implementation details

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

Catalog

A

Data might be stored by length and byte references.

Catalog turns that into the rows and columns we understand.

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

Online Transaction Processing (OLTP)

A

Ensure that, booking airline seats, say, an agent can only access the same seat on at a time.

Is basically concurrency control software.

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

Transactions and their 2 properties

A

Accessing or updating the database.

Must execute correctly while thousands of other transactions take place.

“Isolation” ensures each transaction holds, separate from others.

“Atomicity” ensures either all operations in the transaction takes place or none do.

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

Redundancy - problems with

A

1) Have to perform updates on multiple systems
2) Wastes extra storage space
3) “Inconsistency” - two pieces of data that should be the same are different, because one was updated differently from the other

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

Data Normalization

A

Norm of storing a giving piece of information (like name) in only one place in the database.

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

Controlled Redundance

A

“Denormalization” of data (put in multiple places) so that it can be retrieved all from one file, for example.

Can control against inconsistency by running checks the data hasn’t drifted.

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

Database Schema

A

Description of the database.

Specified during design, it is expected not to change.

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

Steps of database design

A

1) Collect and analyse requirements - Getting user-defined operations/transactions that will be applied to the database
2) Create a conceptual schema - concise description of the data requirements
3) Logical design/data model mapping - actual implementation of the database
4) Physical design - internal storage structures, file organizations indexes, access paths and physical design parameters

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

Entity

A

A thing or object with real world and independent existence.

*Attributes* describe properties of an entity. e.g. EMPLOYE entity maybe described by name, age, address, etc.

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

Types of Entity Attributes

A

Simple (or atomic) = can’t be broken down vs…

Composite = Can be divided into parts (e.g. parts of an address)

Single Valued = age (only one value at a time) vs…

Multi-valued = colors on a car (i.e. can hav multiple values at once)

Stored (birth date) vs…

Derived (age derived by birth date)

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

Entity Type vs. Entity Set/Collection

A

Entity type defines (is the name of) a collection of entities with the same attributes (the intension).

Entity set/collection is a set of actual entities that are part of the same entity type (the extension).

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

Key Attribute

A

The attributes that, together, establish the uniqueness constraint (i.e. that an entity is the one they say it is).

No concept of a “primary key” like in relational database (even though these keys can perform that role).

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

Meta data

A

The structure and constraint info that is stored in the catalog so the dbms can refer to the schema

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

Intension vs extension

A

Intension is Schema.

Extension is DB state.

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

Three-Schema architecture

A

Internal Level or Schema = physical storage and access paths.

Conceptual Level = structure of DB for users (e.g. columns and types).

View or external level = hides part of db so user only sees what’s needed.

The DBMS does “mapping’s” between these layers

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

Logical data independence

A

The ability to change the conceptual Schema (i.e. the columns and types) without changing the view or application schema.

Hard, because the application usually is referring to things as defined in the conceptual Schema.

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

Physical Data Independence

A

Capacity to change the internal Schema (e.g. how the data is laid out on the drive) without changing the conceptual schema.

Easier because data abstraction allows conceptual schema to stay the same. If conceptual schema is the same, an application doesn’t have to change.

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

How to achieve logical and physical data independence?

A

The levels don’t have to change, just the *mappings*

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

Data Definition Language (DDL)

A

If no clear levels, DDL defines conceptual and internal schemes.

If there are clear levels, it defines conceptual.

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

Impedance Mismatch Problem

A

Data structure of the DBMS not matching that of the programming language

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

Steps of database design

A

1) Collect and analyse requirements - Getting user-defined operations/transactions that will be applied to the database
2) Create a conceptual schema - concise description of the data requirements
3) Logical design/data model mapping - actual implementation of the database
4) Physical design - internal storage structures, file organizations indexes, access paths and physical design parameters

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

Functional Data Model

A

When two entities represent their relationship by having each other as attributes.

For example: DEPARTMENT might have employee and EMPLOYEE might have department.

These have to be inverses of each other.

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

Recursive or self-referencing relationships

A

When the same entity is on both sides of a relationship. For example: SUPERVISION has two employee entities involved: one for employee and one for supervisor

Add elements on diagram:

1) Add arrows to show direction of the relationship
2) Add roles names to name what role each party plays in that relationship

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

Cardinality Ratios

A

The constraint on a binary relationship that mimics the “miniworld” constraint. For example: in WORKSFOR the ratio of DEPARTMENT:EMPLOYEE of 1:N means that an employee can only work for one department but many employees can work for a department.

It is always the max number.

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

Participation Constraint or Minimum cardinality constraint

A

Specifies the minimum number of relationships an entity has to participate in.

When an entity has to have AT LEAST some relationship to another entity.

For example, every employee should be under a department.

If it is a full set of employees, it is “total participation” if it is only some (e.g. in MANAGES only some employees manage the department) it is called partial.

This is depicted by a double or bolded relationship line.

38
Q

Structural Constraints

A

The cardinality ratio (i.e. the max relationship) or participation constraints (min relationships) are, collectively, called structural constraints

39
Q

How to name entities

A

Entities are singular, not plural.

Entities and relationships are all caps.

Attributes start with a cap.

Roles are lower case.

Relationships read left to right, top to bottom.

40
Q

What do these shapes mean (see image)?

A

Top to bottom:

Entity

Weak Entity

Relationship

Identifying Relationship

41
Q

What do these shapes mean (see image)?

A

Attribute

Key Attribute

Multivalued Attribute

Composite Attribute

Derived Attribute

42
Q

What do these shapes mean (see image)?

A

Total Participation of E2 in R

Cardinatlity Ration 1:N for E1 : E2 in R

Structural Constraint (min, max) on Participation of E in R

43
Q

Weak Entity vs. Strong Entity types

A

A weak entity types don’t have a key attribute of its own

A regular or strong entity type does have their own key attribute

A weak entity will often have a partial key which uniquely identifies them WITHIN THEIR OWNER entity

44
Q

Identifying a Weak Entity Type

A

Entities belonging to a weak entity type are identified by being related to another entity of a strong entity type + having their own partial key attribute

For example, DEPENDENT entity might be a DEPENDENT_OF an EMPLOYEE. Two DEPENDENTs might have the same info. But they are still separate entities because the exist only in relation to EMPLOYEE.

Weak entities are depicted by double lined boxes and their “identifying relationship” is depcited as a double lined diamond.

Given EMPLOYEE will likely only have one DEPENDENT with the same First_name, First_name is a partial key

45
Q

Weak Entity Type relationships

A

The Strong Entity Type which owns the Weak Entity Type is called the “identifying” or “owner” entity type.

The two entities have a “identifying relationship”.

The weak entity type has “total participation constraint (existence dependency)” with respect to its identifying relationship.

46
Q

ANSI/SPARC Diagram

A
47
Q

Conceptual Schema

A

Describes all conceptually relevant, general, time invariant structural aspects of reality (e.g. the column names you don’t expect to change once you start gathering data).

Does not describe data representation or physical organisation and access.

48
Q

External Schema

A

Describes a parts of the information in the conceptual schema in a form convenient to a particular user group’s view.

It is derived from the conceptual schema.

Basically creates a new view from a query.

49
Q

Internal Schema

A

Describes how the info in the conceptual schema is physically organised for optimal performance.

For example, for fast retrieval, some data might be best represented as a trie whereas other data might be stored as a tree.

50
Q

Physical Data Indepedence

A

Measure of how much the internal schema can change without affecting the applications.

Basically amounts to letting you change the storage of an entity go from a trie to a tree, say, without having to notify the application.

This arises because the conceptual schema doesn’t change.

51
Q

Logical Data Independance

A

Measure of how much you can change the conceptual schema, without changing the applications that run on the database.

Basically can you change the conceptual schema without affecting the application… can but hard because a view based on a query would have to change if the conceptual schema changes.

Much harder than physical data independence.

52
Q

System Metadata = critical for the DBMS

A

Where data came from

How data were changed

How data are stored

How data are mapped

Who owns data

Who can access data

Data usage history

Data usage statistics

53
Q

Business Metadata = critical in a data warehouse

A

What data are available

Where data are located

What the data mean

How to access the data

Predefined reports

Predefined queries

How current the data are

54
Q

Metadata Chart (Lesson 2, Lecture 35)

A

Watch lecture to see how parts relate

55
Q

Relational Model - Data Structures

A

Columns have names

of columns = degree of the table

Rows = cardinality

Table name, column names and data types = schema

Schema = stable over time

56
Q

Integrity vs. Consistency

A

Integrity = Database reflects reality well

Consistency = the database lacks internal conflicts

57
Q

Entity type names have to be unique: T/F?

A

True

58
Q

Property Values can be what form?

A

Lexical (words), visible (photo), audible (sound)

They are things that name other things (e.g. email is a thing but also the name of the user)

59
Q

The value of an identifying property has how many instances?

A

It has at most one instance of the identified entity

(Every entity must be uniquely referenceable)

60
Q

Composite Property

A

A property made up of multiple other properties.

For example: Name may be made up of first name + last name

61
Q

Multi-valued property types

A

Shown by a double ellipse

Means the entity can have multiple values at once.

For example: user/interests could be 3 things: beer, games, reading

62
Q

Partial Function

A

When entities on either side of a relationship don’t necessarily participate in the relationship.

For example: users linked by “MARRIED TO” might include some users not married.

Shown by single, unboldd line.

63
Q

Total Function

A

When all entities in a set must belong in a relationship.

Shown by a bolded line in lectures

Double line in book.

64
Q

Is many to many (N:M) relationship a function?

A

No. A function maps one independent value to a dependent value.

So in math terms, many to many is a “relationship”

65
Q

Can a ternary relationship (three entitites all connected via one, central, super-relationship) be represented by a conjugation of binary relationships (a series of unilateral relationships between all three of the entities)?

A

No. The binary relationships each specify a relationship, but can’t specify an instance that includes all three relationships.

For example, a user can be on a team, a team at an event, a user at an event (binary relationships)… doesn’t mean that the user was on the team at that event (ternary relationship).

66
Q

A double diamond means?

A

An identifying relationship with a weak entity type owned by a strong entity type.

The weak entity can only be identified by its ‘partial identifier’ plus the key of the strong entity type.

NOTE: If there is a chain of weak entities, each being owned by the other, ALL the partial identifies up to the strong entity are needed to identify it.

67
Q

Supertypes and subtypes

A

A subtype has to be a member of the supertype.

A “d” denotes disjoint, meaning member of one subtype can’t also be of the other subtype.

An “o” denotes overlap, meaning a member of one subtype can also be of the other subtype.

Subtypes seem to have arrows pointing to the supertype.

68
Q

Sub types and super types - Inheritance

A

If a supertype USER has properties email and name, all subtypes will inherit those properties.

BUT, subtypes might ALSO have their own properties, that the supertype won’t have.

69
Q

Union entity

A

Union implies that

a) entity is a subtype of the other entity types (i.e. every member of the subtype is in ONE of the supertypes)
b) that there is NO intersection between the supertypes… the subtype doesn’t belong to BOTH supertypes… only one

70
Q

Abstraction in EER

A

Classification - Supported

Aggregation - Not supported by EER

Generalisation - Supported

<check></check>

71
Q

Why are there no EER database products?

A

Lack of closed query based language

(was in lecture 32, lesson 4)

72
Q

Components of any data or relational model

A

Data structures

Constraints

Operations:

1) Algebra
2) Calculus (either tuple (SQL) or domain calculus (QBE))

73
Q

Does the order of columns or rows matter?

A

NO! Big deal

74
Q

Arrow pointing from one entity to the other?

A

Means that the first entity is a subset of the one it points to

75
Q

Constraints - Primary Key

A

Means that in the entity, it can’t be Null

Also, if any other entity has that property, it must be a subset of the entity that has that as a primary key

76
Q

Specialization

A

The sub-classes entities of a superclass are a sepcialization.

For example university might have PERSON superclass with specializations of EMPLOYEE, ALUM, STUDENT.

77
Q

Specialization hierarchy vs. Lattice

A

Hierarchy implies every subclass only has one superclass.

Lattice implies a subclass can have more than one superclass.

For example: A STUDENTASSISTANT is a subclass under both STUDENT and EMPLOYEE.

78
Q

Two constraints for specialization

A

Disjointness Constraint = subclasses can either be disjoint (members can only belong to one subclass) or overlapping (members can belong to multiple subclasses)

Completeness Constraint = a superclass can have total specialization (every member must be in at least one subclass) or can have partial specialization (a member need not be in any subclass)

79
Q

Shared Subclass

A

The name for when a subclass has more than one superclass.

They have multiple inheritance in that they inherit attributes from their many superclasses.

80
Q

What to do if only single inheritance is allowed?

A

You have to create other entities that represent the combined entities.

For example, if you have EMPLOYEE, ALUM and STUDENT you might need E, A, S, EA, ES, AS and EAS.

81
Q

Union type or category

A

An entity can be a SUBSET from a union of other superclass entities (it might not have all items in the superclasses).

For example: three owner types of a vehicle could be BANK, PERSON, COMPANY. They can form a union called OWNER which has a circle with a “u” in it and a union symbol.

A member of OWNER will be ONE OF the superclass types.

NOTE: This is distinct from shared subclasses with multiple inheritance. In those, a member of a subclass entity belongs to ALL superclass entities (e.g. STUDENT + EMPLOYEE is TEACHINGASSISTANT)

82
Q

Total Union vs. Partial Union

A

A total union is symbolized by the bold or double line.

It means it has all the items that are the union of its superclasses.

83
Q

Thoughts on Models

A
  • Models are means of communicating
  • Users of models must have some shared knowledge
  • Emphasizes some aspects
  • Has a language
  • Can be erroneous
  • Can have aspects that don’t exist in real life (contour lines)
84
Q

When to use a DBMS?

A
  • Data intensive apps
  • Persistent storage of data
  • Centralized control of data
  • Control of redundancy
  • Control of consistency and integrity
  • Multi user support
  • Sharing of data
  • Data documentation
  • Data independence
  • Control of access and security
  • Backup and recovery
85
Q

When not to use a DBMS?

A
  • When investment in software, hardware, experince is too high
  • When generality not needed (overhead for security or concurrency too high)
  • Data and apps are simple and stable
  • real-time requirements can’t be met
  • Multi-user not needed
86
Q

Model represents?

A

Perception of structures of reality.

87
Q

What does data modeling comprise (from lecture)?

A

Fix perceptions of reality (EER good for this)

Represent the perception (Relational good for this)

Aside: we then select aspects of model to fix and abstract them.

88
Q

Components of Data Model

A
  • Data Structures
  • Constraints
  • Operations
  • Keys and identifiers
  • Integrity and consistency
  • Null values
  • Surrogates
89
Q

Hierarchical Model

A

As distinct from the relational model or entity model.

Was the first database model. Was used on an IBM computer in the ’70s. Still used in XML today.

90
Q

Name-based vs. surrogate-based representations

A

Name-based = The rows depend on names for things like email or name or address… if you get something with different names, could be a different person OR the person just changed their details

Surrogate based uses a unique identifier that can’t change for the person. So if other fields change, you still know it is the same person.

CS6400 (3 decks)

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