Brainscape's Knowledge GenomeTM

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


See full index

CMPUT 291 > Lecture 2- Relational Data Model > Flashcards

Lecture 2- Relational Data Model Flashcards

1
Q

Why do you need a data model?

A
  • Need a model for describing the structure of data and constraints
  • And operations on data
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What are the three layers in a data model schema?

A
  1. External
  2. Conceptual
  3. Physical
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is the physical level of a data model schema?

A

Problems working with data = routines hard coded to deal with physical representation

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

What are some characteristics of the physical level of data model schema?

A
  • Diffucult to change the physical representation
  • Application code becomes complex since it must deal with details
  • Rapid implementation of new features impossible
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Describe the conceptual level of a data model schema

A
  • Hides details
  • Presents data as a set of tables
  • Mapping from conceptual to physical schema done by DBMS
  • Physical schema can be changed without changing applications (refered to as physical data independence)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Describe the basic idea of relational database

A
  • Organize data as a set of tables
  • View each table as a set of rows
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What are some advantages to relational databases?

A
  • Simple
  • Solid mathematical foundation (set theory)
  • Powerful query languages
  • Efficient query optimizers
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is a relational database?

A

A set of relations (tables)

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

A relationship consists of what?

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

What is an instance?

A

Table content, with rows and columns

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

What is a schema?

A

Table structure, with name and type of columns

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

More formally, a relation is a set of ______ or ______

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

What is the cardinality, degree and domain of this example?

A
  • Cardinality = 3
  • Degree = 5
  • Domain = name: char(16), age: {1,…,100}
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is domain?

A

The set of values from which the values of an attribute are drawn

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

Why use querying relations?

A
  • Queries can be written intuitively and the DBMS is responsible for efficient evaluation
  • Precise semantics for relational queries
  • Allows the optimizer to extensively re-order operations and still ensure that the answer does not change
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is this example doing?

A

Creating a relations in SQL (CREATING a TABLE)

17
Q

What is this example doing?

A

Insert a single tuple

18
Q

What is this example doing?

A

Delete all tuples that satisfy a condition

19
Q

What are integrity constraints?

A

Conditions that hold for any instance of the database (eg. domain constraints)

20
Q

____ are defined when schema is defined

A

Integrity constraint

21
Q

A ____ of a relation is one that satisfies all specified ICs

A

Legal instance (DBMS should not allow illegal instances)

22
Q

Integrity constraints avoid _______

A

Avoids data entry errors

23
Q

What is a primary key constraint?

A

A set of fields is a key for a relation if it is both unique (no two distinct tuples can have the same values in all key fields) and minimal (no subset of a key is a key

24
Q

A relation can have ______ key

A

Can have more than one key

25
Q

Candidate key vs primary key

A
  • Canditate key = all keys of the relation
  • Primary key = one defined by DBA
26
Q

What is a superkey?

A

1st condition holds but the 2nd may not

27
Q

A table can only have one _______

A

Primary key

28
Q

What is a foreign key?

A

Set of fields in one relation that refers to a tuple in another relation

29
Q

A foreign key must correspond to ______

A

A primary or candidate key of the other relation

30
Q

Describe NO ACTION, CASCADE, SET NULL/SET DEFAULT, RESTRICT

A
  • NO ACTION= delete/update is rejected
  • CASCADE= also delete all tuples that refer to deleted tuple
  • SET NULL/SET DEFAULT= set foreign key value of referencing tuple
  • RESTRICT= similar to NO ACTION
31
Q

Describe

A
  • External level= applications can access data through some views, different views for different categories of users, a view is computed, mapping from external to conceptual schema is done by DBMS
  • Views= a view is just a relation, but we store a definition, rather than a set of tuples, views can be dropped using the DROP VIEW command
  • Views and security= views can be used to present necessary information while hiding details in underlying relations

CMPUT 291 (7 decks)

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