COP4710 Midterm

What is the result NULL or true

true

In many-to-one relationship between account and customer (many accounts can belong to a single customer), the foreign key will be in what relation? a)Account b)Customer c)Can be in any way

A

What is a database instance? Can you explain it with one word?

A database instance is now the database looks currently

Difference between logical and physical schema

A logical schema deals with the design of the database and the relationships in it, while a physical schema deals with the way data is stored.

Explain the difference between primary and a foreign key?

A primary key is a candidate key chosen as the principal means of identifying tuples within a relation. A foreign key is a relation schema may have an attribute that corresponds to the primary key of another relation.

What is functional dependency

A relationship between two attributes, typically between the PK and other non-key attributes within a table.

What is the difference between view and table

A view is just the snapshot of a table at a certain time and you can have multiples tables in one view A table however can be actually manipulated and only information in that table is seen.

You were asked to add a column to the Company table which will hold the amount of shares sold for a company. Constraint to add. The field value can not be empty. Write a sql for that Find the number of stock purchases per customers city. The result should return the customers cities and the count of all stock purchases per city, in descending order. For example we might have two customers from Miami who made 2 purchases each so we get 4 purchases City | Count ----------------- Miami | 4 Seattle | 2 Austin | 1 For bonus points, make the necessary changes to your query above so that it show only cities that have more than one stock-buyer. Expected result: City | Count ----------------- Miami | 4 Seattle | 2

ALTER TABLE company (ADD COLUMN shares_sold integer NOT NULL) company(company_name, city, price, num_share) customer(SSN, name, city) stock_purchase(purchase_id, company_id, SSN, how_many, purchase_date) SELECT c.city, count(c.city) FROM customer c INNER JOIN stock purchase s ON c.SSN = s.SSN GROUP by c.city ~HAVING count (c.city) > 1~ bonus ORDER BY count DESC

Tell the difference between an left-outer, right-outer and full outer join

Left outer: A join in which all rows from the table on the left will be included regardless of whether they match rows from the table on the right Right outer:A join in which all rows from the table on the right will be included regardless of whether they match rows from the table on the left Full outer: All of the values from both tables

CRUD

Create, Read, Update, Delete

What is the result NULL and false

FALSE

Multivalued attributes are a good idea

False

Name the mapping cardinalities

One to one One to many Many to one Many to many

What is Boyce-Codd Normal Form

One of the forms of database normalization. A database table is in BCNF if and only if there are no non-trivial functional dependencies of attributes on anything other than a superset of a candidate key.

Given the following relations, use relational algebra to find the requested data in the statement below Student (sID, surName, firstName, campus, email, cgapa) Course(dept., cNUM, name, breadth) Offering (oID, dept., cNum, term, instructor) Took(sID, oID, grade) Find the cumulative gpa (cgpa) of all students who gotten an A in at least one course

PROJECT cgpa (SELECT grade = "A" (Took) NAT. JOIN Student)

Part B.2 to problem Based on your E-R diagram and relational algebra representation, use relational algebra to find the requested data in the statements below company(company_name, city, price, num_shares) customer(SSN, name, city) stock purchase(purchase_id, company_name, SSN, how_many, purchase_date) Find the number of share purchases for the company "Apple Inc."

PROJECT count(purchase_id) (SELECT company_name = "Apple Inc."(stock_purchase))

Part B.1 to problem Based on your E-R diagram and relational algebra representation, use relational algebra to find the requested data in the statements below company(company_name, city, price, num_shares) customer(SSN, name, city) stock purchase(purchase_id, company_name, SSN, how_many, purchase_date) Find the names of all stock buyers that have bought stock from companies located in the same city as their city of residence

PROJECT name (SELECT city = tmp.city(stock_purchase NAT. JOIN customer NAT JOIN company as tmp))

Given the following relations, use relational algebra to find the requested data in the statement below Student (sID, surName, firstName, campus, email, cgapa) Course(dept., cNUM, name, breadth) Offering (oID, dept., cNum, term, instructor) Took(sID, oID, grade) Find the student number (sID) of all students who have taken csc343

PROJECT sID(SELECT dept. = "csc" and cNum= "343"(Offering) NAT. JOIN Took)

Explain the difference between a procedural and non-procedural languages

Procedural: user specifies what data is required and how to get those data Non-Procedural: user specifies what data is required without specifying how to get those data

There is a difference between a table and a relation schema?

True

Explain shortly what are unary and binary relationships

Unary, the entity has a relationship with itself Binary, two entities have relationship with each other

Give a short example of a data inconsistency situation

When there is repetition of data and you change the first instance, but the rest remains the same

What's an atomic domain?

An atomic domain is one in which all of its members are indivisible

What's a derived attribute?

An attribute whose value comes from another attribute (ex: age is derived from DBOS)

What is a weak entity?

An entity that lacks a prmary key and forms one with the primary key of another set

what is a null value

An unknown value

What is concurrency in a database transaction?

Any number of users can use the same database at the same time

In a one-to-one relationship between account and customer, the foreign key will be in what relation? a)Account b)Customer c)Can be in any way

C

Part C to problem Based on your ER diagram and relational algebra representation, write SQL queries for the following statements Create Table statement for the table Customer customer (SSN, name, city)

CREATE TABLE customer( SSN varchar(10) not null unique, Name varchar(100) not null, City varchar (100), PRIMARY KEY (SSN) );

Part A to problem Draw an E-R diagram, and based on your E-R diagram, write all the relation schemas in a relational algebra way, underlining the primary keys Requirements: the customer is a stock trading company, store this information: The public companies the stock buyers (customers) Stock purchases company (company_name, city, price, num_shelves) customer(SSN, name, city) stock purchase(purchase_id, company_name, SSN, how_many, purchase_date)

Customer---(one to many)---Stock Purchase---(many to one)---Company

DML or DDL statements? ALTER TABLE x ADD COLUMN a

DDL

DML or DDL statements? CREATE TABLE x()

DDL

DML or DDL statements? DELETE FROM table WHERE condition

DML

DML or DDL statements? SELECT a FROM t1 JOIN t2 on t1.a = t2.a

DML

DML or DDL statements? UPDATE table 1 SET attr2 = "new value" WHERE attr1 = "xyz"

DML

What are DML and DDL

DML and DDL refer to the languages used to access and manipulate data in a database. (SQL)

DDL stands for

Data Definition Language

DML stands for

Data Manipulation Language

Generalization and Specialization are known to be the same concept, but from a different perspective. Explain that concept.

Generalization is combining elements with the same attribute names and types. Specialization is breaking apart elements into more specific types.

What is the difference between a WHERE clause and a HAVING clause

HAVING happens after the table has been grouped WHERE happens before the table has been grouped

Explain total vs partial participation of a set in a relationship

In total participation, all if the records from one entity will have a relationship with the record from the other set. In partial, only some records will have the relation.

What is the result NULL and NULL

NULL

What is the result NULL and true

NULL

What is the result NULL or NULL

NULL

What is the result NULL or false

NULL

Data models

Relational model and object-oriented model

Name all relational algebra operations

Select Project Union Set difference Cartesian product Rename: Division Natural Join Aggregate functions

What is the First Normal form?

Sets the fundamental rules for database normalization and relates to a single table within a relational database system.

What is a database management system (DBMS)?

Software that handles the storage, retrieval, and updating of data in a computer system.

What is the Second Normal form?

The second step in normalizing a database. . A relation is in 2NF if it is in 1NF and every non-prime attribute of the relation is dependent on the whole of every candidate key

What is a domain

The set of allowed values for an attribute

What is the Third Normal form

The third step in normalizing a database and it builds on the first and second normal forms, 1NF and 2NF. 3NF states that all column reference in referenced data that are not dependent on the primary key should be removed.