ISYS 4283 UARK Harmon Midterm

Question 1

are established between entities in a well-structured database so that the desired information can be retrieved

Answer 1

Relationships

Question 2

A person’s name, birthday, and social security are all examples of:

Answer 2

Attributes

Question 3

A person, place, object, event, or concept about which the organization wishes to maintain data is called a(n)

Answer 3

entity

Question 4

A database is an organized collection of ______ related data

Answer 4

logically

Question 5

When normalizing, the goal is to decompose relations with anomalies to produce smaller, well-structured relations

Answer 5

True

Question 6

The _______ states that no primary key attribute may be null

Answer 6

entity integrity rule

Question 7

A relation is in first normal form if it has no more than one multivalued attribute

Answer 7

False

Question 8

A primary key is an attribute that uniquely identifies each row in a relation

Answer 8

true

Question 9

An attribute (or attributes) that uniquely identifies each row in a relation is called a:

Answer 9

primary key

Question 10

In the figure below, the primary key for “order line” is which type of key:

Answer 10

Composite: In database design, a composite key is a candidate key that consists of two or more attributes (table columns) that together uniquely identify an entity occurrence (table row).

Question 11

A rule that states each foreign key value must match a primary key value in the other relation is called the

Answer 11

referential integrity constraint

Question 12

When all multivalued attributes have been removed from a relation is it said to be in

Answer 12

first normal form

Question 13

are anomalies that can be caused by editing data in tables

Answer 13

modification

Question 14

A relation that contains no multivalued attributes and has non-key attributes solely dependent on the primary key but contains transitive dependencies is in which normal form

Answer 14

Second

Question 15

An appropriate datatype for one wanting a fixed-length type for last name would include:

Answer 15

Char

Question 16

The smallest unit of application data recognized by system software is a

Answer 16

field

Question 17

A decimal datatype would be appropriate for a zip code

Answer 17

false

Question 18

A method for handling missing data is to

Answer 18

track missing data with special reports

Question 19

One method to handle missing values is to substitute a value estimated with a formula

Answer 19

true

Question 20

A relationship where the minimum and maximum cardinality are both one is a(n)

Answer 20

mandatory one

Question 21

Data that describes the properties of other data are

Answer 21

metadata

Question 22

What is Lisa Simpson’s Address

Answer 22

SELECT address, city, state, zip, FROM student WHERE first_name = “Lisa” AND last_name = “Simpson”;

Question 23

How many students are named ned?

Answer 23

SELECT COUNT (*) FROM student WHERE first_name = “Ned”;

Question 24

How many classes are there broken down by department?

Answer 24

SELECT department, COUNT(course_ID) FROM course GROUP BY department;

Question 25

Where are different departments

Answer 25

SELECT DISTINCT departments, FROM course;

Question 26

Add new student: Homer Simpson 742 Evergreen Terrace Springfield, OR 97475

Answer 26

INSERT into student( first_name, last_name, address, city, state, zip) VALUES ('Homer', 'Simpson', '742 Evergreen Terrace', 'Springfield', 'OR', '97475');

Question 27

Update Marge Simpson's zip to 11111 & her city to Fayetteville (She's a student)

Answer 27

UPDATE student SET zip='11111', city='Fayetteville' WHERE first_name = "Marge", AND last_name = "Simpson";

Question 28

Create Student Table

Answer 28

CREATE TABLE Student( student_ID integer not null PRIMARY KEY, first_name varchar(50) not null, Last_name varchar(50) not null, Address varchar(50), City varchar(50), State char(2), zip char(5), );

Question 29

create student course table

Answer 29

CREATE TABLE student_course ( student_id integer not null FOREIGN KEY REFERENCES student(student_id), Course_id interget not null FOREIGN KEY REFERENCES course(course_id), grade decimal(3,2) not null, PRIMARY KEY(student_ID, course_ID), );

Question 30

How many products do we stock? Label the field.

Answer 30

SELECT COUNT(*) AS num_prod, FROM products;

Question 31

What was the date of the most recent order

Answer 31

SELECT MAX(order_date) AS lastorder, FROM orders;

Question 32

What was the average total price of all items ordered?

Answer 32

SELECT AVG(quantity*item_price) AS avg_tot_price, FROM OrderItems;

Question 33

What is an entity

Answer 33

Person, place, object, event, concept (often corresponds to a row in a table)

Question 34

What are attributes?

Answer 34

Properties or characteristics of an entity or relationship type (often corresponds to a field in a table

Question 35

Describe the difference between required and optional attributes

Answer 35

* Required- must have a value for every entity (or relationship) instance with which it is associated * Optional- may not have a value for every entity (or relationship) instance with which it is associated

Question 36

Describe and explain relationships

Answer 36

– link between entities(corresponds to primary key-foreign key equivalencies in related tables) - Have cardinalities, one to one, one to many, many to many, can be mandatory or optional

Question 37

What are primary keys?

Answer 37

An attribute (or combination of attributes) that uniquely identifies individual instances of an entity type. Also called Identifier key

Question 38

What are foreign Keys

Answer 38

Attributes that links two tables together, refers to a primary key in another entity

Question 39

What is the entity integrity rule?

Answer 39

The entity integrity rule states that for every instance of an entity, the value of the primary key must exist, be unique, and cannot be null

Question 40

What is referential integrity rule

Answer 40

requires that a foreign key must have a matching primary key or it must be null.

Question 41

Explain cardinality and the different types

Answer 41

The number of instances of one entity that can or must be associated with each instance of another entity: - Minimum cardinality: if zero, then optional, if one or more than mandatory - Maximum cardinality: the maximum number

Question 41

One-to-one

Answer 41

Each entity in the relationship will have exactly one related entity

Question 42

one-to-many

Answer 42

An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity

Question 43

many-to-many

Answer 43

Entities on both sides of the relationship can have many related entities on the other side

Question 44

what is an associative entity

Answer 44

(bridge table): Associative entity is like a relationship with an attribute, but it is also considered to be an entity in its own right. Used to resolve many to many relationships, links entities together

Question 45

Composite keys are described as what

Answer 45

Composite Keys are described as what? -Composite- required 2 fields to create a unique identifier for the row, typically used when braking up many-to-many relationships. A composite key is a combination of 2 keys to make a unique identifier

Question 46

What is data normalization?

Answer 46

The process of decomposing relations with anomalies to produce smaller, well-structured relations

Question 47

What are 1NF

Answer 47

First normal form- no multivalued attributes, every attribute value is atomic, all relations are in 1st normal form

Question 48

What is 2NF

Answer 48

1NF plus every non-key attribute is fully functionally dependent on the ENTIRE primary key -Every non-key attribute must be defined by the entire key, not by only part of the key -No partial functional dependencies

Question 49

How to get into 2nd normal form

Answer 49

remove all partial transparencies

Question 50

Partial dependency

Answer 50

non-key attribute dependent on part of the primary key, ex. productdescrip, prodfinish,prodprice, partial on prodID

Question 51

transitive dependency

Answer 51

primary key is determinant for another attribute which in turn is a determinant for the 3rd ex. customeraddress on customername on customerID

Question 52

Why do we normalize a database

Answer 52

Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data, goal is to end redundancy and avoid anomalies

Question 53

What is 3NF

Answer 53

2NF plus no transitive dependencies

Question 54

How do we get to 3NF

Answer 54

Remove transitive dependency Ex. taking customer ID out of OrderID and making it its own thing

Question 55

Types of strings:

Answer 55

-char- fixed length( remaining space is filled with blanks) -varchar- variable length

Question 56

Numeric strings:

Answer 56

-Integer- allows whole numbers -2.7 mil to 2.7 mil -tinyint- 0-255 -smallint- -32000- 32000 -bigint -decimal(p,s)- precision and scale

Question 57

Date and time string

Answer 57

-datetime -date -time

Question 58

What does “*” do in SQL

Answer 58

It means all columns, it is a way to retrieve all info

Question 59

SELECT Clause

Answer 59

List the columns and expressions to be returned from the query, what do you want to find EX. SELECT address

Question 60

FROM clause

Answer 60

indicate the table or view from which data will be obtained EX. FROM student

Question 61

WHERE clause

Answer 61

indicate the conditions under which a row will be included EX. WHERE first_name="john"

Question 62

What is the GROUP BY clause and why is it useful

Answer 62

is used to arrange identical data into groups with the help of some functions. GROUP BY is used with the SELECT statement ex. SELECT deptID, FROM course, GROUP BY first_name;

Question 63

What is the HAVING clause?

Answer 63

restricts the results of a GROUP BY in a Select Expression. Always used with GROUP BY. HAVING Clause restricts the data on the group records rather than individual records. EX. SELECT deptID, FROM employee GROUP BY deptID HAVING AVG(Salary) >3000

Question 64

Can you do mathematical operators in SQL

Answer 64

yes, you can do +,-, *, / or through SELECT or AVG(), COUNT(), MAX()

Question 65

When preparing to create a table, one should:

Answer 65

Name of new table specified after CREATE statement, Name and definition of table columns separated by comma

Question 66

INSERT INTO does what?

Answer 66

Adds one or more rows to a table EX. INSERT INTO customer SELECT * FROM customer WHERE state='TX'

Question 67

What is alter table

Answer 67

ALTER TABLE statement allows you to change column specifications EX. ALTER TABLE vendors ADD vend_phone char (20) or DROP COLUMN vend_phone char (20)

Question 68

What is the proper order in a SELECT statement of the following: SELECT, HAVING, GROUP BY, FROM, ORDER BY, WHERE

Answer 68

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY