5160 Databases Uncc

Front 1

What is a DBMS?

Back 1

A Database Management System is a software package designed to maintain and utilize databases.

Front 2

What are five reasons for using a DBMS?

Back 2

*Data independence and efficient access * Data integrity and security * Uniform data administration * Concurrent access, recovery from crashes * Reduced application development time

Front 3

What is a data model

Back 3

A collection of concepts for describing data

Front 4

What is a schema

Back 4

a description of a particular collection of data, using the given data model

Front 5

What is the relational model of data

Back 5

Based on the concept of a relation, basically a table with rows and columns. Every relation has a schema which describes the columns or fields.

Front 6

That are the different levels of abstraction?

Back 6

Many different views, the conceptual (logical) schema and the physical schema

Front 7

What is the Conceptual schema

Back 7

Conceptual schema defines logical structure

Front 8

What is the Physical schema

Back 8

The Phys sch describes the files and indexes used

Front 9

What is DDL?

Back 9

Data definition language defines views and schemas;

Front 10

What is logical data independence?

Back 10

Protection for the application from changes in logical structure of data

Front 11

What is Physical data independence?

Back 11

Protection for the application from changes in the physical structure of the data.

Front 12

What does ACID stand for ((very important))

Back 12

Atomicity- all actions in the trans happen or none at all; Consistency- If each trans is consistent, and the DB starts consistent, it ends consistent; Isolation - Execution of one trans is isolated from that of other trans; Durability - If a transact commits, its effects persist

Front 13

What component of the DBMS guarantees Atomicity & Durability?

Back 13

The recovery manager

Front 14

What is the motivation of concurrency control?

Back 14

Consistency and Isolation

Front 15

Define a relationship in an ER mode? What is a relationship set?l

Back 15

Association among 2 or more entities;

Front 16

What is a key constraint?

Back 16

A restriction on a relationship that affects the key. Eg: an employee can work in many departments, a department can have only one manager per store.

Front 17

What is a participation constraint?

Back 17

This is a constraint that requires every key value in a table to participate in a relationship with another entity's key (total) or none at all (partial)

Front 18

How is a participation constraint shown in the ER model?

Back 18

Total participation is shown with a bold (or double) connector between relations, a partial participation is an unbolded line. A key constraint is an arrow toward the one in a one-to-many relationship.

Front 19

What is a weak entity?

Back 19

It can be identified uniquely only by considering the primary key of another (owner) entity.

Front 20

What is an ISA hierarchy?

Back 20

If we declare A ISA B, every A is a B; It is shown as a triangle with ISA inside.

Front 21

What is the difference between an overlap and covering constraint?

Back 21

In an ISA hierarchy, on overlap constraint determines if an employee can be both Hourly and Contract; Covering says whether and employee has to be one or the other.

Hint 21

isa

Front 22

What is the reasons for using an ISA hierarchy?

Back 22

To add descriptive attributes specific to a subclass and to identify entities that participate in a relationship; signifies inheritance

Front 23

What is aggregation?

Back 23

It allows us to treat a relationship set as an entity set for purposes of participation in (other) relationships; It is shown by a dotted rectangle around the aggregated entity set.

Front 24

What is conceptual design?

Back 24

It follows requirements analysis and uses ER diagrams.

Front 25

What is the difference between cardinality and degree/arity?

Back 25

Cardinality is the number of rows and fields is the degree/arity

Front 26

What is a relation?

Back 26

A relation is a set of distinct rows or tuples.

Front 27

What is the current standard of SQL?

Back 27

SQL-92; Nobody uses SQL-99 yet

Front 28

How, in SQL, do you create a Students table with three fields?

Back 28

CREATE TABLE Students (fieldone CHAR(20), fieldtwo CHAR(20), fieldthree INTEGER)

Front 29

How do you get rid of a table? How do you alter it?

Back 29

DROP TABLE Students

ALTER TABLE Students
ADD COLUMN firstYear: INTEGER

Front 30

How do you add a tuple?

Back 30

INSERT INTO Students (fieldone, fieldtwo, fieldthree)
VALUES (53699, 'Green', ITIS)

Front 31

How do you delete a tuple?

Back 31

DELETE
FROM Students S
WHERE S.name='Smith'

Front 32

To find all 18 year old students

Back 32

SELECT *
FROM Students S
WHERE S.age=18

Front 33

What is an integrity constraint?

Back 33

A condition that must be true for any instance of the database; Created when schema is defined; checked when relation in modified.

Front 34

What is a domain constraint

Back 34

integer, char or other datatype for a field

Front 35

A set of fields is a key for a relation if:

Back 35

No two distinct tuples can have the same values in all key fields and this is not true for any subset of the key.

Front 36

What is a superkey? A candidate key?

Back 36

A set of columns within a table whose values can be used to uniquely identify a row. A candidate key is a minimal set of columns necessary to identify a row.

Front 37

How do you designate a primary key in SQL?

Back 37

CREATE TABLE Enrolled (sid CHAR(20), PRIMARY KEY grade CHAR(20))

Front 38

What is a foreign key?

Back 38

A set of fields in one relation that is used to refer to a tuple in another relation. It must correspond to primary key of the second relation.

Front 39

How do you designate a foreign key in SQL and why would you?

Back 39

FOREIGN KEY (name, addr) REFERENCES Customer_info
Designating a foreign key helps to reinforce referential integrity and prevent invalid entries.

Front 40

What happens if a row with a non-existent foreign key is inserted

Back 40

The insertion is rejected or assigned either a null or default foreign key

Front 41

What happens to a record in Table2 if the primary key row in Table1 that matches to the Table2 foreign key is deleted?

Back 41

Either delete all foreign key tuples that refer to it * disallow deletion of a primary key row that is referred to by a foreign key * assign it a default key * or set the foreign key to null.

Front 42

How to create a View in SQL?

Back 42

CREATE VIEW Viewname(field1, field2) as select s.field1, s.field2 from Students S where s.field1>3.0

Front 43

True or False: When the owner entity is deleted, all owned weak entity must also be deleted.

Back 43

True

Front 44

How do you show a cascade delete?

Back 44

When designating a foreign key:
FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE CASCADE)