We use the WMCRM database and here is the summary of the database schema (where schema is used in its meaning of a summary of the database structure):

VEHICLE (InventoryID, Model, VIN)

SALESPERSON (NickName, LastName, FirstName, HireDate, WageRate, CommissionRate, OfficePhone, EmailAddress, InventoryID)

CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, EmailAddress, NickName)

PHONE_NUMBER (CustomerID, PhoneNumber, PhoneType)

CONTACT(ContactID, CustomerID,ContactDate,ContactType,Remarks)

Where

InventoryID in SALESPERSON must exist in InventoryID in VEHICLE

NickName in CUSTOMER must exist in NickName in SALESPERSON

CustomerID in PHONE_NUMBER must exist in CustomerID in CUSTOMER

CustomerID in CONTACT must exist in CustomerID in CUSTOMER

To update the values we use UPDATE…SET statement as follows:

update customer

set LastName = 'Griffey'

where CustomerID = 3;

QUESTION 1: if you use update statement without WHERE clause, what can happen?

If we want to delete the record you use the following script:

delete

from customer

where CustomerID = 8;

QUESTION 2: If you use delete statement without WHERE clause, what can happen?

The SQL Drop Table Statement

The SQL DROP TABLE statement drops the table’s structure along with all of the table’s data. For example, to drop the contact table and all its data, you use the following SQL statement:

drop table contact

However, you can not drop customer table before drop contact table, because the SQL DROP TABLE statement does not work if the table contains or could contain values needed to fulfill referential integrity constraints.

QUESTION 3: Can you drop table salesperson? If yes, how do you do it? If no, why?

The SQL Alter Table Statement

To drop the customer table, you must first drop the phone_number table or at least delete the foreign key constraint PHONE_CUS_FK. This is one place where the ALTER TABLE command is useful. You use the SQL ALTER TABLE statement to add, modify, and drop columns and constraints. For example, you can use it to drop the PHONE_CUS_FK constraint with the statement:

alter table phone_number

drop constrain PHONE_CUS_FK;

After either dropping the phone_number table or the PHONE_CUS_FK foreign key constraint, you can then successfully drop the customer table.

Question 4: do you have another method to drop table salesperson?

1. Write three command statements to back up the `Orders` Table for data only, structure with data and structure only using mysqldump. 2. Write the command statement to import the data using mysqlimport from customers.csv file. 3. What's the difference to use the LOAD DATA LOCAL INFILE vs mysqlimport. 4. Write SQL statements: a. Create a database user "cis495_dev1" with password "dolphins". b. Create the database "cis495Demo". c. Grant user "cis495_user1" with "select" ,"delete" and "execute" permission to DB "cis495Demo". d. Revoke the privilege "delete" for the user "cis495_user1". 5. List three ways ( SQL statement: update, alter, set) to change the password from "dolphines" to "icando" for the user "cis495_user1". 6. Explain the difference between user and role.

Using the SQL for Dummies textbook in the CSU Online Library, refer to Table 3-4 “Types of Protection” on page 74 to create three scenarios in which the use of protection operations are used to secure a database. Describe the scenario, select which protection operations users should use in the scenario, and then explain your selection.

Your paper should be three pages in length. All sources used, including the textbook, must be referenced; paraphrased and quoted material must have accompanying citations, and cited per APA guidelines.

E-commerce Web sites typically interact with or affect many different areas of an organization. Because of this interaction, e-commerce Web sites typically include functionality to assist in the communication that is required between the site and other parts of the organization. Sales or accounting may need reports. Marketing or sales may need the ability to update the product catalog. Manufacturing may need access to inventory levels, and the list goes on. In a typical large organization, the operational needs of the organization are available already through the main Web site.

Your e-commerce site needs to integrate with the following other software applications:

• Inventory Application
• Accounting
• Marketing

The next step in your E-Commerce Implementation Plan is to prepare for the integration of the e-commerce Web site with the above software applications. You will also design the database for your e-commerce application and create the database structure in any open-source database (such as SQL Server Express Edition, PostgreSQL, or others). Please refer to the unit resources for some articles that will help you complete this assignment.

The project deliverables are as follows:

• Create a section in the E-Commerce Implementation Plan document, and name it "Integration."
• Draw the process diagram that shows how your e-commerce site integrates with inventory application, accounting, and marketing.
• Design the architecture diagram that shows how the e-commerce site database integrates with other software applications.
• Design the e-commerce site ER diagram.
• Implement the database structure for the e-commerce site in any open-source database

Please provide diagrams

6.23 LAB: Python insert/update sqlite3 datafiles

Given is a Python program that connects to a sqlite database and has one table called writers with two columnns:

• name - the name of a writer
• num - the number of works the writer has written

The writers table originally has the following data:

name, num
Jane Austen,6
Charles Dickens,20
Ernest Hemingway,9
Jack Kerouac,22
F. Scott Fitzgerald,8
Mary Shelley,7
Charlotte Bronte,5
Mark Twain,11
Agatha Christie,73
Ian Flemming,14
J.K. Rowling,14
Stephen King,54
Oscar Wilde,1

Update the Python program to ask the user if they want to update entries or add new entries. If the name entered already exists in the writers table then the database record is updated, overwriting the original contents. If the name does not exist in the writers table, then add a new record with the writer's name and number of works. The following TODO sections must be completed.

• Check if a writer exists in the writers table
• If the writer exists in the table, locate an entry to be updated by writer's name and update the writer's value for num
• If the writer does not exist in the table, add a new entry in the writers table and provide the value for name and num

Ex: If the input is:

y
J.K. Rowling
30
y
Elton John
y
62
n

The output is:

(ID, Name, Num)
(1, 'Jane Austen', 6)
(2, 'Charles Dickens', 20)
(3, 'Ernest Hemingway', 9)
(4, 'Jack Kerouac', 22)
(5, 'F. Scott Fitzgerald', 8)
(6, 'Mary Shelley', 7)
(7, 'Charlotte Bronte', 5)
(8, 'Mark Twain', 11)
(9, 'Agatha Christie', 73)
(10, 'Ian Flemming', 14)
(11, 'J.K. Rowling', 30)
(12, 'Stephen King', 54)
(13, 'Oscar Wilde', 1)
(14, 'Elton John', 62)

6.23 LAB: Python insert/update sqlite3 datafiles

Given is a Python program that connects to a sqlite database and has one table called writers with two columnns:

• name - the name of a writer
• num - the number of works the writer has written

The writers table originally has the following data:

name, num
Jane Austen,6
Charles Dickens,20
Ernest Hemingway,9
Jack Kerouac,22
F. Scott Fitzgerald,8
Mary Shelley,7
Charlotte Bronte,5
Mark Twain,11
Agatha Christie,73
Ian Flemming,14
J.K. Rowling,14
Stephen King,54
Oscar Wilde,1

Update the Python program to ask the user if they want to update entries or add new entries. If the name entered already exists in the writers table then the database record is updated, overwriting the original contents. If the name does not exist in the writers table, then add a new record with the writer's name and number of works. The following TODO sections must be completed.

• Check if a writer exists in the writers table
• If the writer exists in the table, locate an entry to be updated by writer's name and update the writer's value for num
• If the writer does not exist in the table, add a new entry in the writers table and provide the value for name and num

Ex: If the input is:

y
J.K. Rowling
30
y
Elton John
y
62
n

The output is:

(ID, Name, Num)
(1, 'Jane Austen', 6)
(2, 'Charles Dickens', 20)
(3, 'Ernest Hemingway', 9)
(4, 'Jack Kerouac', 22)
(5, 'F. Scott Fitzgerald', 8)
(6, 'Mary Shelley', 7)
(7, 'Charlotte Bronte', 5)
(8, 'Mark Twain', 11)
(9, 'Agatha Christie', 73)
(10, 'Ian Flemming', 14)
(11, 'J.K. Rowling', 30)
(12, 'Stephen King', 54)
(13, 'Oscar Wilde', 1)
(14, 'Elton John', 62)

This format should allow you more time to think about your answer. The length of your answers should range from 2-4 paragraphs, depending on the complexity of the question. Remember to use the readings and your class notes to answer the questions. Use citations/quotes and then go on to interpret and explain the quote in your own words.

Feminist economists offer a different view of the economy by focusing attention on caring labor and the care economy. Why do they focus on care and what is meant by the care penalty? Finally, briefly explain some of the findings of empirical research on care work and the care penalty.  

Part #4: Data normalization:

Background: Data in a relational database is stored in a normalized form. Data normalization or just normalization is a strategy used to organize data into multiple related tables to reduce data redundancy while preserving data integrity.

Exercise: Normalize the student data in the University table (sample data is shown further below) into 3 tables, namely, Student, Department, and Course. You do not need to populate data. Just illustrate the schema for the 3 tables. You may make suitable assumptions to normalize the data. Ensure you introduce additional columns as needed to preserve relationships between the 3 tables.

Show just the schema (without any data) for the normalized form of the data with 3 tables in the space below:

Estimating benefit of normalization

The objective of normalization is to reduce redundant data in a database. Assume the University table has 100 rows -- that is 100 students in 1 course taught by 1 department. Each row has 8 columns. So the total number of columns in the database (which has just 1 University table) is 100 * 8 = 800 cells.

Now, compute similar information using the normalized version of your tables. In the normalized tables, indicate how many rows & total number of columns that would be present and fill-in the following table:

If each cell occupies 100 bytes, what would be the percentage difference in size of the size of the two databases? Compute the answer by completing the data in the following tables: