Using the world_x database you installed on your MySQL Workbench, create a new table named “independence” with the following attributes (columns):

A field named “id” which has data type auto_increment,

A field named “country_name” which has data type varchar(50), and

A field named “independence_date” which has type “date.”

After you create the table, run the following SQL command:

INSERT INTO independence(country_name, independence_date) VALUE (‘United States’,’1776-07-04’)

Submit a 1-page Microsoft Word document that shows the following:

The SQL command you used to create the “independence” table and a list of the rows in the “independence” table.

Explain the purpose of the field named “id.” Why do you not have to include it in an insert statements? How would change the incrementing value to a number greater than one?

Database Systems

Lab Exercises

1. Create a table Faculty based on the following chart:

Save the SQL statement as ex1.sql. Confirm and validate the creation of the new table.

2. Create a table Dept based on the following chart:

Save the SQL statement as ex2.sql. Confirm and validate the creation of the new table.

3. Add a new column Location to Dept table which has data type Char(7). Save the SQL statement as exsql. Confirm and validate the modification of the table.
4. Add a new FOREIGN KEY constraint (named faculty_dept_fk) to column Dept on Faculty table that refers to column Dept_Code on Dept table. Add ON DELETE CASCADE option. Save the SQL statement as exsql. Confirm and validate the modification of the table.

5. Increase Last_Name column to 25 characters long. Save the SQL statement as exsql. Confirm and validate the modification of the table

use this to draw https://www.diagrameditor.com/

Consider the University Database with the following relations:

Professors (pid, pname, dept, ext) Students (sid, sname, major-dept, year)

Courses (cid, cname, dept, credithours) Enrollment (sem-year, sid, cid, grade)

Teaches (pid, cid, sem-year, class-size)

where,

Professors: All professors have professor id (pid), name (pname), department that they work (dept), and a phone number extension for their office (ext).

Students: All students have id (sid), name (sname), department for their major (major-dept), and a year (year i.e, freshman, sophomore, junior, etc).

Courses: All courses have a course id (cid), course name (cname), department (dept), and total credit hours (credithours).

Enrollment: has a semester year (sem-year), enrolled student id (sid), course id (cid), and grade that student earns (grade).

Teaches: has a professor id (pid), course id (cid), semester year (sem-year), and class size (class-size).

Attributes “dept” in relations Professors and Courses, and attribute “major-dept” in relation Students have the same domain, and have values like “CDS”, “EE”, “CE”, etc. Attribute “sem-year” has values like “Spring2016”, “Fall2015”, etc. Assume that cids are unique, i.e. if there are multiple sections of a course, each section has a unique cid. (Please ask for clarifications if you have questions about the relations and/or the semantics of the following queries!).

Express the queries below using Relational Algebra.

1. Find cids and names of courses in which every student majoring in “CDS” enrolled in “Fall2015”.

2. Find sids, names and major-dept of students who enrolled in a course that is taught by professor James. (10 pts)

3. Find pid and names of professors who teach no courses in “Fall2015”. (10 pts)

4. Find cid and cname of courses that are offered by “CDS” department that are taught by professors who are from another department in “Fall2015". (20 pts)

5. Find pid and names of professors who teach only courses offered by “CDS” department. (20 pts)

6. Find pnames and pids of professors who teach every course offered by “CDS” dept. (20 pts)

7. Find sids of students who enroll in “Fall2015” every 3 credit hour course offered by “CDS” department. (20 pts)

Note: There is no database to test against

Create a stored procedure (SP) called Add_Worker that will add an employee to a factory. This SP has two parameters:

a) A parameter (WorkerId) that the SP can read or write to. When the procedure is called it will contain the PK value that should be used to add the row.

b) A parameter (WorkerName) that the SP can read only, it contains the new worker's username.

c) A parameter (Result) that the SP can write to. If the new record is added successfully, the parameter will be set to the value 'Entered Successfully'. If the add fails the parameter is set to the value 'Unsuccessful'.

Declare an exception named WORKER_NOT_ADDED. Associate the number -20444 with this exception.

Execute an insert statement to add the row to the table. Use the values of the passed parameters on the insert statement. Today's date should be used for the Created_date column. Use 'abcuser' for the CreatedBy column.

If the workerID is added:

a) Set the parameter 'Result' to 'Entered Successfully'

b) Commit the changes

Next, add exception processing to the SP

a) For all exceptions, set the parameter 'Result' to 'Unsuccessful'

b) If the WORKER_NOT_ADDED exception occurs, display the message 'New WorkerId already exists on the table.'

c_ For any other error, display the error number and the message associated with that error number.

Benis firm has hired you to develop a database to keep information about its sales offices. It has a number of sales offices in several towns. We need to store unique office number and location for each sales office. Each sales office is assigned to one or more employees. You need to store the following information for each employee; unique employee id, employee name, salary and phone number(s). An employee must be assigned to only one sales office. For each sales office, there is always one employee assigned to manage that office. An employee may manage only the sales office to which he/she is assigned. The firm lists property for sale. Attributes of property include a unique property_Id and Location which is made up of Address, City, and Zip Code. Each property must be listed with one (and only one) of the sales offices. Each unit of property has only one owner. For each owner we need to store unique owner_id and owner name. An owner may own one or more properties, and we need to keep the percentage of properties owned by each owner.

REQUIRED:

a) List down the entities, their key attributes and foreign attributes if any.

b) Draw an E-R diagram using the above information, clearly showing the

     cardinalities and ordinalities.                                                                         

c) Differentiate between a multi-valued attribute and a composite attribute giving an

      example extracted from the passage.                                                            

                                                                                              [TOTAL: 25 Marks]

For Network Access Control and Cloud Security (Cloud Database Encryption Technology Based on Combinatorial Encryption)

You are required to research and report on this topic according to the Detail of Question below.

A. understand in order to present three main parts:

1. Summary:

o Provide a 200-300 word summary of the paper under review, from the background to the results being presented, and further work proposed. Please do NOT copy the abstract into this space!

2. Main points:

o The main issues as you see them.

o This is different than the summary.

3. Strengths and Weaknesses:

o Provide some critical analysis of the paper under review, positive and/or negative.

I have this exercise and i dont know how to do it

DDB Corporation was formed by twenty-four shareholders on January 1, 2017. The shareholders will be having their semi-annual meeting on September 28, 2018 to review the financial results for January 1 – June 30, 2018. As of January 1, 2018, the company has a retained deficit (this means the company incurred a net loss in 2017).

The following are the unadjusted balances of DDB Corporation as of June 30, 2018. Although the accounts are all shown with a positive balance, with the exception of retained earnings, they have the normal debit or credit balance that accounts in their account type have (e.g. – assets have a debit balance, liabilities have a credit balance).

Accounts payable 95,000

Accounts receivable 75,000

Accumulated depreciation 28,000

Additional paid-in capital 15,000

Cash 56,960

Common stock 12,000

Depreciation expense 8,000

Rent expense 14,000

Income tax expense 22,736

Income tax payable 22,736a.

Interest expense 1,000

Interest payable 1,000

Inventory 16,000

Cost of goods sold 87,540

Note payable - current portion 10,000

Note payable - long-term 50,000

Payroll taxes payable 6,000

Prepaid expenses 5,000

Property, plant, & equipment 100,000

Retained deficit 35,000

Product revenue 330,000

Deferred revenue 6,000

Advertising expense 61,000

Salary expense 93,500

You have been hired by DDB Corporation to prepare the financial statements. The company has provided you with the following information necessary to record adjustments required to show accurate financial statements.

1. On June 27, 2018, the bookkeeper received $20,000 for a shipment to be sent on July 8, 2018. The bookkeeper debited Cash and credited Product Revenue on June 27.

2. The company performed a physical inventory count on June 30, 2018 and determined that the inventory value on hand was $25,000.

3. The unadjusted balance in the Prepaid Expense account includes prepayment on an advertising contract. The balance in Prepaid Expenses as June 30, 2018 should include:

1. Advertising - January 1 - December 31, 2018 annual contract, original contract amount was $60,000.
2. Rent - July 2018 payment of $2,000. When the bookkeeper paid this on June 29th, he debited Rent Expense and credited Cash.

4. Depreciation expense for the 6 months ended June 30, 2018 should be $10,000.

5. On January 1, 2018, the company borrowed $60,000 at a 5% annual rate. The loan is to be paid back to the bank annually on December 31st in three equal installments beginning December 31, 2018. Each payment will include the interest incurred for that year. The interest for the year should be expensed evenly throughout the year. (Hint: there are two adjustments to be made)

6. Income tax expense must be re-calculated after all adjustments have been recorded. The income tax rate is 35%.

Requirements:

(1) Show trial balance spreadsheet. Reference each adjustments with the letter used above.

(2) show properly formatted income statement and balance sheet based on the adjusted balances from your trial balance spreadsheet. List each account separately in your statements (for example, do not have one amount on your income statement labeled "operating expenses"). . Per share information is not given so you do not need to include earnings per share on your income statement.

(3) Calculate the net profit margin and current ratio for the financial statements.

(4) Show memo to the shareholders reporting the company's financial results for the six months ended June 30, 2018. Include a brief explanation of your ratio analysis results from requirement (3). Also, the company is considering the purchase of a $75,000 piece of equipment. One plan to acquire the equipment calls for a $50,000 cash payment with the remainder financed on a six-month loan. Assuming all account balances other than those accounts affect by the equipment purchase remain the same, include an explanation of the effect of this financing arrangement on the company's current ratio. Ignore interest expense on the new loan.