A. Project 1 case: DDL’s and Business Rules - The SQL queries for this project are based on the business rules from PROJECT 1. Thus, the SQL queries will run against the tables from Project 1 (see section B below). The following is a list of the business rules derived from the Project 1 assignment:

- A university is identified by its name and located in a particular city.

- A university has many researchers, each of whom can only be associated with only one university.

- A researcher is identified by an identification number, and has a name, phone, and email address.

- A researcher can attend many conferences.

- A conference is identified by an identification number; it also has a name, a location, and a date.

- For each conference, a researcher may or may not present a scientific research paper.

- For each conference, one university is in-charge of its logistic, while another university is in-charge of its marketing.

- A university can be involved in either role with any conference.

B. Run the following DDL’s and add your own data rows to the tables

CREATE TABLE `university` (

`UNIVERSITY_ID` int(11) NOT NULL,

`UNIVERSITY_NAME` varchar(45) DEFAULT NULL,

`UNIVERSITY_CITY` varchar(45) DEFAULT NULL,

`UNIVERSITY_STATE` varchar(2) DEFAULT NULL,

PRIMARY KEY (`UNIVERSITY_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `conference` (

`CONFERENCE_ID` int(11) NOT NULL,

`CONFERENCE_NAME` varchar(45) DEFAULT NULL,

`CONFERENCE_CITY` varchar(45) DEFAULT NULL,

`CONFERENCE_DATE` date DEFAULT NULL,

`UNIVERSITY_ID_LOGISTICS` int(11) NOT NULL,

`UNIVERSITY_ID_MARKETING` int(11) NOT NULL,

`CONFERENCE_STATE` varchar(2) DEFAULT NULL,

PRIMARY KEY (`CONFERENCE_ID`,`UNIVERSITY_ID_LOGISTICS`,`UNIVERSITY_ID_MARKETING`),

KEY `fk_CONFERENCE_UNIVERSITY1_idx` (`UNIVERSITY_ID_LOGISTICS`),

KEY `fk_CONFERENCE_UNIVERSITY2_idx` (`UNIVERSITY_ID_MARKETING`),

CONSTRAINT `fk_CONFERENCE_UNIVERSITY1` FOREIGN KEY (`UNIVERSITY_ID_LOGISTICS`) REFERENCES `university` (`UNIVERSITY_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,

CONSTRAINT `fk_CONFERENCE_UNIVERSITY2` FOREIGN KEY (`UNIVERSITY_ID_MARKETING`) REFERENCES `university` (`UNIVERSITY_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `researcher` (

`researcher_id` int(11) NOT NULL,

`res_lname` varchar(45) DEFAULT NULL,

`res_fname` varchar(45) DEFAULT NULL,

`res_title` varchar(45) DEFAULT NULL,

`university_UNIVERSITY_ID` int(11) NOT NULL,

PRIMARY KEY (`researcher_id`,`university_UNIVERSITY_ID`),

KEY `fk_researcher_university1_idx` (`university_UNIVERSITY_ID`),

CONSTRAINT `fk_researcher_university1` FOREIGN KEY (`university_UNIVERSITY_ID`) REFERENCES `university` (`UNIVERSITY_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `conference_has_researcher` (

`CONFERENCE_CONFERENCE_ID` int(11) NOT NULL,

`RESEARCHER_RESEARCHER_ID` int(11) NOT NULL,

PRIMARY KEY (`CONFERENCE_CONFERENCE_ID`,`RESEARCHER_RESEARCHER_ID`),

KEY `fk_CONFERENCE_has_RESEARCHER_RESEARCHER1_idx` (`RESEARCHER_RESEARCHER_ID`),

KEY `fk_CONFERENCE_has_RESEARCHER_CONFERENCE_idx` (`CONFERENCE_CONFERENCE_ID`),

CONSTRAINT `fk_CONFERENCE_has_RESEARCHER_CONFERENCE` FOREIGN KEY (`CONFERENCE_CONFERENCE_ID`) REFERENCES `conference` (`CONFERENCE_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,

CONSTRAINT `fk_CONFERENCE_has_RESEARCHER_RESEARCHER1` FOREIGN KEY (`RESEARCHER_RESEARCHER_ID`) REFERENCES `researcher` (`researcher_id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

C. Data Queries   “Good questions to ask of your data” – The business rules from section A above were used to derive the following 10 query descriptions below. Read each description and write the equivalent SQL query.

1. List the universities sorted by city.

2. List the Researchers sorted by last name.

3. List the researchers and their corresponding universities sorted by the university city. You must use an INNER JOIN with RESEARCHER and UNIVERSITY tables.

4. List the university and “count” of researchers at each university sorted by the university name.

You CANNOT use an inner join. You must use a subquery with RESEARCHER.

5. List the university and “count” of researchers at each university where there is more than 1 researcher. Also, sort this query by the university name. You must user an INNER JOIN (NOT A SUBQUERY)

6. List all conferences in the state of georgia. Include the city and state with the query.

7. List the conference and researcher count for conferences in georgia. You CANNOT use an inner join. You must use “two” subqueries that are nested.

8. List the researcher, university and conferences where the conference is in California. You must user an INNER JOIN (NOT A SUBQUERY).

9. List the university and count of researchers that are planning to present at a conference in 2021. You CANNOT use an inner join. You must use a subquery.

10. List the Universities in the database table. If the university appears in a conference that is in charge of its marketing. Each university can only be listed once. You must user an OUTER JOIN (NO INNER JOIN OR SUBQUERY).

I have part A completed. I am stuck on part C mostly, but help on part B would be greatly appreciated.

Create tables according to the mapping. Add 2 records to each. Create 5 queries for database of 3 table joins to use most of the tables or group of tables in database. You should not have tables that are of no use.

Student(ssn, name, major)

Class(classID, name, f_ssn)

Faculty(ssn, name, office_num, dept_id)

Department(Dept_id, office_num, f_ssn)

Enroll(s_ssn, classID, grade)

Professor(f_ssn, alma-mater, tenured)

Instructor(f_ssn, term_degree, type)

Lecture(classID, method)

Lab(classID, location)

Person(ssn, dob, gender)

INTRO TO DATABASE

Consider the Sailors-Boats-Reserves database described below.

Sailors(sid: integer, sname: string, rating: integer, age: real)

Boats(bid: integer, bname: string, color: string)

Reserves(sid: integer, bid: integer, day: date)

Write each of the following queries in SQL.

1. Find the names and ages of sailors who have reserved at least two boats.

2. For each boat reserved by at least 2 distinct sailors, find the boat id and the average age of sailors who reserved it.

Presence on the Web and Internet are critical components of just about any business, large or small. However, having a website presence can increase the security risks and threats to which an organization is subject. Consider the questions below and respond to at least two of them.

• What risks do Web and database attacks create for an organization?
• What harm could result from a successful attack?
• What roles do security analysts play in preventing Web server, application, and database attacks?
• How do these roles differ in different types of organizations?

1. Discuss database constraints: Primary key, check, and referential integrity constraints? Give an example for each.
2. What are the three types of database design situations?
3. Briefly describe the various tasks of the primary key. Explain the concept of a foreign key. Explain the concept of a surrogate key.
4. Explain the essence of normalization that is implemented through the use of normal forms.
5. What is SQL? Explain why it is important to learn SQL.
6. What is the purpose of normalization? What conditions are required for a relation to be in 3NF?
7. What are the advantages and disadvantages of normalization?
8. Explain denormalization, and why it may be appropriate to denormalize a set of tables.

Company X database

Create an ER Diagram using UML notation for the following tables, then write out in the Relational model.

Company X is a manufacturing company that binds and sells books. They have hired you to create database to track their Employees, Products, customers and their orders. All employees work with book Binding, Only some are designated as Reps for customers. Reps may have many customers.

But each customer can only have 1 rep. They count to record each Books author, title, price, published year, publisher, and ISBN#

Construct an ER diagram for a database system that models data of the following situation. You are creating a database for a stock trading company. The company has clients and financial officers. Each client is either an individual or another legal entity (e.g. another company). A client has a name, social security number, address, contact information, the date that entered the database system, and also a ranking that the trading company keeps internally (high, med, low). Financial officers have a name, employee ID, social security number,annual salary, bonus, bonus. There are contracts that one client (or more clients together in one contract) have with the hedge fund. Each contract has a contract id, budget and task. For  One constraint is that each contract has exactly one financial officer that is responsible and at least one client. There can also be more than 1 financial officer in 1 location. There is one office location for hedge fund with address and phone number. Draw an ER diagram for the above description.

Write a C program that creates a database of plant height samples. In this scenario a researcher enters a name of a group/type of plants, and heights for each plant in the group/type Use input will be stored in a database file, which should be a file called database.csv and should be in the following formatPlant1, number_of_samples, sample1, sample2, sample3 ...Plant2, number_of_samples, sample1, sample2, sample3 ...Plant3, number_of_samples, sample1, sample2, sample3 ...Lines starts with the name of a plant type, then an integer indicating the number of following samples. Each sample represents the height of a plant.In your program you should declare a function “input_sample_set” that prompts the user to input plant name and sample count. In this function dynamically allocate memory an array of ints for each plant sample and read in each sample value. Append the data to the database file. Prompt the user “y” or “n” to continuously re-run “input_sample_set” if y is entered. Make sure you deallocate memory after each run of “input_sample_set” to avoid memory leaks.

Prepare a 400 words essay:

You are a participant in an information systems project to design a vehicle theft database for a state law enforcement agency. The database will provide information about stolen vehicles (e.g., autos, golf carts, SUV, and trucks), with details about the vehicle theft as well as the stolen vehicle itself. These details will be useful to law enforcement officers investigating the vehicle theft.

- Identify 10 data attributes you would capture for each vehicle theft incident. How many bytes should you allow for each attribute? Give an example of the data you would store in each field.

- Specify which attribute from your list you designate as the primary key

- Discuss briefly: should the database include data about the status of the theft investigation? If so, what sort of data needs to be included?

- Discuss briefly: Can you foresee any problems with keeping the data current? Explain

Please new answer, about 400 words.

By definition, each relational database table must contain exactly one candidate key.

Select one:

a. True

b. False

Values appearing in a key column must always be unique, regardless of the type of key.

Select one:

a. True

b. False

Which of the following statements about well-formed relations is FALSE?

Select one:

a. A relation is well-formed if it is in third normal form (3NF)

b. Every determinant in a well-formed relation is also a candidate key

c. Any table that meets all of the necessary criteria to be classified as a relation can be considered well-formed

d. Well-formed relations are generally not susceptible to modification anomalies

Each table in a relational database must be related to at least ________ other table(s).

Select one:

a. Zero

b. One

c. Two

d. Three

Data contained within a relational database are stored in two-dimensional ________.

Select one:

a. Folders

b. Lists

c. Catalogs

d. Tables