![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
Here are some links to some ER tools you could use for your projects and assignments. You most definitely need one of these tools to do data modelling for this class. These are only suggestions, so please feel free to use any other tool that is available to you.
Purchase from Microsoft: Microsoft Visio
http://office.microsoft.com/en-us/visio/
This might save people some money if they want to buy visio.
https://my.umuc.edu/psp/uucepprd_newwin/EMPLOYEE/EMPL/e/?url=http%3a%2f%2fumuc.e-academy.com
Freeware Tools:
https://wiki.gnome.org/action/show/Apps/Dia?action=show&redirect=Dia
https://www.lucidchart.com/pages/b?utm_expid=39895073-40.cV9vRzTxQMmhllfotWNAPA.1
http://ermaster.sourceforge.net/
You have been tasked to create a database for your college to track students, their courses (past, present, and future), grades and academic standing, and instructors.
Describe the entities and their attributes that might be required for this application, the type of database processing required, and the application software needed.
Consider all users of the application and how the issues of data integrity, security, and user interfaces would best be handled.
I believe the type of database processing required for this system is relational database because RDB is suited to manage constructive data which requires concurrency and data consistency.
In designing a relational database, each relation needs to be an one-to-many connection. Therefore, I created Enrolled Courses entity between Students entity and Courses entity, and Student Degree Plans entity between Students entity and Academic Disciplines entity in which academic majors and minors are defined.
Since the system is simple and small scale, Microsoft Access is enough to start.
Consider a student club or organization in which you are a member. What are the data entities of this enterprise? List and define each entity. Then, develop an enterprise data model showing these entities and important relationships between them.
Answer just one of the following questions. Be sure to read other student posts and work to not duplicate information that has already been provided.
1) What is Dr. Codd's relational model and what does it include
2) Contrast the terms relation, tuple, and attribute with table, row, and column.
3) Describe the relationship between a superkey, a candidate key, and a primary key. Give an example.
4) Explain where you can use business rules in an organization.
5) Describe and give an example of one of the following:
3) Describe the relationship between a superkey, a candidate key, and a primary key. Give an example.
A super key of a relation is an attribute or a subset of attributes which identify a unique tuple.
A candidate key of a relation is one of super keys which is a minimal set of attributes to identify a specific tuple. This key name is derived from a candidate for a primary key.
A primary key of a relation is a candidate key which is the best to identify a tuple. The values of this key are required to be unique for each tuple in a relation.
For example, when there are multiple attributes named City ID, City name, County name, and State name in a relation for cities of United States, All of City ID, the subset {City ID, City name}, the subset {City ID, State name}, the subset {City ID, City name, County name, State name}, and the subset {City name, County name, State name} are super keys. On the other hand, Candidate keys in this relation are City ID and the subset {City name, State name}. Either City ID or the subset {City name, State name} can be the primary key. If you set City ID as the primary key, the subset {City name, State name} would be an alternate key.
For each of the following descriptions:
a. A piano manufacturer wants to track all pianos it makes. Each piano has a unique serial number and a manufacturing completion date. Each instrument represents exactly one piano model, all of which have an identification number and model. The company produces thousands of pianos of a certain model, and the design is specified before any single piano exists.
b. A vendor builds multiple types of tablet computers. Each has a type identification number and a name. The key specifications for each type include amount of storage and display type. The company uses multiple processor types, exactly one of which is used for a specific tablet type. The same processor can be used in multiple types of tablets. Each processor has a manufacturer and a manufacturer's unique code that identifies it.
perform the following tasks:
1. Identify the degree and cardinalities of the relationship. 2. Express the relationships graphically with an E-R diagram.
Document your work into a single, well-organized, well-written word document and submit no later than due date.
Provide your own unique example of a supertype/subtype relationship. Be sure to describe the relationship and discuss if the disjoint or overlap rule would apply to your example.
You are working for country club with thousands of members. You have been tasked with designing a database to keep track of the members and their guests.
The club keeps track of mail and telephone contact information, name and membership number. When a member joins the club they can become a social member with pool, racket ball and weight room privilieges or golf member which includes all of the social member privileges plus access to the golf course.
Develop, document and design an EER for this situation.
Submit your word document no later than the due date.
Conduct some research on Oracle Data dictionary views using Google or other search engines.
Pick a view and describe and provide at least two columns in the view.
Be sure to pick a dictionary view no one else has already selected.
Instructions:
This project allows students to demonstrate their skills in the area of designing relational databases to satisfy specific business rules and requirements. The deliverables for this project include an Entity Relationship Diagram and detailed documentation describing the database design and structure.
Project 1 Details:
In this project you will be provided with a description of an application (below) to create an entity-relationship diagram (ERD) and design accompanying table layout using sound relational modeling concepts and practices. The relationships between the entities and the attributes for the entities will be identified and described. This database will provide the foundation for the follow-on project. The following paragraphs provide the background and summary of the business requirements.
You are a database consultant with Ace Software, Inc. and have been assigned to develop a database for the Mom and Pop Johnson video store in town. Mom and Pop have been keeping their records of videos and DVDs purchased from distributors and rented to customers in stacks of invoices and piles of rental forms for years. They have finally decided to automate their record keeping with a relational database.
You sit down with Mom and Pop to discuss their business, and watch their operation for about a week. You discover quickly that a video and a DVD are both copies of a movie kept in a separate plastic case that is rented out. They have several copies of each movie they rent; therefore there are several videos and DVDs for each movie title. You learn that in their inventory they have several thousand videos and DVDs, which they get wholesale from about a half dozen distributors. The video and DVD prices to them are based on the quantity of their shipment and the past business they have done with each company.
The price of a DVD for a movie might be different than the price of a video for the same movie, even from the same distributor. Each distributor provides different types of movies (e.g., suspense, horror, mystery, comedy, etc.). A single distributor may provide several different types of movies in both video and DVD format. It is possible to obtain the same movie from multiple distributors, and at different wholesale prices.
Each video and DVD has a unique identification number that Mom and Pop assign in their inventory, in addition to the distributor's serial number for the item. Each movie also has a unique identification number Mom and Pop assign in addition to the title, and any movie IDs the distributors use in their electronic catalogs. Distributors provide electronic catalogs to Mom and Pop and the information from these catalogs must be included in the database.
Mom and Pop need to record when a video or DVD is rented, when a video or DVD is returned, and all customer charges such as late and damaged fees, failure to rewind fees, and taxes. They need a report of which videos are returned late because there are standard and late charges. On occasion there are discount prices for certain movies or types of movies. Customers want to rent movies based on actors or actresses, running length, type of movie, rating, year released, the director, and the academy awards won (by the movie, the actors, the actresses and/or the directors). Customers also want to know how many videos they have rented in the last month, year, and so forth. Mom and Pop need to keep only basic information on customers in their database, such as name, address, telephone numbers, etc.
There must be no limit to the number of video and/or DVD copies of a movie that Mom and Pop can have in their inventory. Video/DVD ID numbers, movie ID numbers, and distributor ID numbers for videos, DVDs, and movies are all different. Also, each movie must be able to have an unlimited number of actors, actresses, directors, and academy awards (i.e., Oscars). Other types of awards (e.g., Golden Globe, People's Choice, etc.) are not of interest for this application. The rental of equipment, sale of videos, DVDs, popcorn, etc., is not to be kept in the database.
Using this information, you should:
Step 1) (7 points) Determine and list your entities. Then create relationship sentence pairs between those entities that are related. You should not have any many-to-many relationships.
Step 2) (13 points) Create an entity/relationship diagram (ERD) showing all your entities, attributes, and relationships. Sketch your ERD by hand or use a drawing program. Your diagram must be on a single page. All entities should be relatated to at least one other entity. Your ERD should have all one-to-many relationships and not have any many-to-many relationships.
Step 3) (5 points) Create metadata that describes the table created from each entity and the column created from each attribute in the ERD. Particular attention will be given to the proper specification of all primary key (via "PK") and foreign key (via "FK") columns in the table layouts. These should match your ERD exactly.
Follow the procedures in the course modules and the Database Development Phases document for this project.
Submit everything for the above steps in a single Microsoft Word file to the LEO Project #1 assignment link. Make sure your work is neat and legible and your steps are in order.
Choose Oracle datatypes for the following attributes from a normalized relation including:
Vendor (VendorID, Address, ContactName)
Item (ItemID, Description)
PriceQuote (VendorID, ItemID, Price)
Describe why you selected the datatypes for each attribute.
Give your own example of a relationship that is in 3rd Normal Form.
Be sure to discuss why it is in 3rd Normal Form.
There are three tables: Order, Customer, and Product. The Customer table is in second normal form because it has a transitive dependency of IndustoryID on IndutryName.
To remove the transitive dependency, I divided them into Customer table and Industry table. Then, all of the tables became in third normal form.
You only need to make a single posting each week. You do NOT need to respond to every question.
1) Explain why it's a good idea to have DROP TABLE statements at the beginning of your DDL SQL script files that are used to create one or more tables.
2) How can you drop a table from your database that has one or more other tables referencing it with foreign keys?
3) What is a HAVING clause used for and how does this differ from a WHERE clause?
4) Give an example of how a subquery can be used in the WHERE clause of a SELECT statement.
5) Give your own example of SQL containing one of the following commands: CREATE, DELETE, UPDATE, SELECT, INSERT
First of all, I defined the following tables: customers, orders, and products in one-to-many relationships.
I created examples of SQL for the tables above for question 4 and 5.
4) Give an example of how a subquery can be used in the WHERE clause of a SELECT statement.
CMIS320_Homework1.pdf/* Retrieves records of which customers purchased something from June 1st, 2015 to June 30th, 2015 from the customers table. */
SELECT *
FROM customers
WHERE customer_id IN ( SELECT customer_id
FROM orders
WHERE purchase_date
BETWEEN '6/1/2015' AND '6/30/2015' );
5) Give your own example of SQL containing one of the following commands: CREATE, DELETE, UPDATE, SELECT, INSERT.
/* Creates the customers table. */
CREATE TABLE customers
(
customer_id number(5,0) NOT NULL UNIQUE,
registration_date date,
active varchar2(1),
name char,
gender varchar2(1),
age number(3,0),
address char,
phone_number char,
PRIMARY KEY (customer_id)
);
/* Deletes the record of customer id number 100 from the customers table. */
DELETE
FROM customers
WHERE customer_id = 100;
/* Changes the active status of customer id number 100 from TRUE to FALSE. */
UPDATE customers
SET active = 'f'
WHERE customer_id = 100;
/* Inserts a record into the customers table. */
INSERT INTO CUSTOMERS (
registration_date,
active,
name,
gender,
age,
address,
phone_number
) VALUES (
'6/28/2015',
't',
'John Smith',
'm',
'30',
'123 Cherry Street, Naha, Okinawa 9011234, JAPAN',
'+81-98-123-4567'
);
Instructions:
In this project you will perform the physical design and implementation using SQL Data Definition Language (DDL) and proceed with populating the Mom and Pop Johnson Video Store database via Data Manipulation Language (DML) SQL commands.
Each of the steps below requires a SPOOL file to be submitted. Be sure your SPOOL file contains your SQL statements along with the Oracle responses and/or displayed results. Do NOT submit your SQL script files. Only submit your output SPOOL files. If you are using iSQL*Plus you must screen snapshots as necessary of your SQL and the results.
Project 2 Details:
Submit your SPOOL file(s) showing that all SQL in your SQL script file worked properly. Show the actual SQL statements executed and the results the SQL produced below the code. Do NOT submit your SQL script files. Also, submit all of your .sql files.
/* Create Sequences */ CREATE SEQUENCE SEQ_academy_awards_id INCREMENT BY 1 START WITH 1; CREATE SEQUENCE SEQ_academy_awards_movies_id INCREMENT BY 1 START WITH 1; CREATE SEQUENCE SEQ_actors_id INCREMENT BY 1 START WITH 1; CREATE SEQUENCE SEQ_actors_movies_id INCREMENT BY 1 START WITH 1; CREATE SEQUENCE SEQ_charges_id INCREMENT BY 1 START WITH 1; CREATE SEQUENCE SEQ_charges_rentals_id INCREMENT BY 1 START WITH 1; CREATE SEQUENCE SEQ_customers_id INCREMENT BY 1 START WITH 1; CREATE SEQUENCE SEQ_directors_id INCREMENT BY 1 START WITH 1; CREATE SEQUENCE SEQ_directors_movies_id INCREMENT BY 1 START WITH 1; CREATE SEQUENCE SEQ_distributors_id INCREMENT BY 1 START WITH 1; CREATE SEQUENCE SEQ_inventories_id INCREMENT BY 1 START WITH 1; CREATE SEQUENCE SEQ_movies_id INCREMENT BY 1 START WITH 1; CREATE SEQUENCE SEQ_rentals_id INCREMENT BY 1 START WITH 1; /* Create Tables */ CREATE TABLE academy_awards ( id number(5,0) NOT NULL, category varchar2(25) NOT NULL, year_awarded number(5,0) NOT NULL, is_winner varchar2(1) NOT NULL, is_nominee varchar2(1) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE academy_awards_movies ( id number(10,0) NOT NULL, academy_award_id number(5,0) NOT NULL, movie_id number(5,0) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE actors ( id number(5,0) NOT NULL, first_name varchar2(15) NOT NULL, middle_name varchar2(15), last_name varchar2(15) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE actors_movies ( id number(10,0) NOT NULL, actor_id number(5,0) NOT NULL, movie_id number(5,0) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE charges ( id number(3,0) NOT NULL, -- standard_charge -- late_fee -- damaged_fee -- failure_to_rewind_fee -- other charge_type varchar2(30) NOT NULL, charge number(19,4) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE charges_rentals ( id number(10,0) NOT NULL, rental_id number(10,0) NOT NULL, charge_id number(3,0) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE customers ( id number(5,0) NOT NULL, first_name varchar2(15) NOT NULL, middle_name varchar2(15), last_name varchar2(15) NOT NULL, street_address varchar2(30) NOT NULL, city varchar2(15) NOT NULL, state varchar2(5) NOT NULL, zip_code number(10,0) NOT NULL, phone_number varchar2(20) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE directors ( id number(5,0) NOT NULL, first_name varchar2(15) NOT NULL, middle_name varchar2(15), last_name varchar2(15) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE directors_movies ( id number(10,0) NOT NULL, director_id number(5,0) NOT NULL, movie_id number(5,0) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE distributors ( id number(3,0) NOT NULL, name varchar2(40) NOT NULL, shipment_quantity number(5,0), PRIMARY KEY (id) ); CREATE TABLE inventories ( id number(10,0) NOT NULL, movie_id number(5,0) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE movies ( id number(5,0) NOT NULL, distributor_id number(3,0) NOT NULL, title varchar2(50) NOT NULL, -- suspense -- horror -- mystery -- comedy -- other movie_type varchar2(15) NOT NULL, running_length number(5,0) NOT NULL, rating number(3,0), year_released number(5,0) NOT NULL, is_video varchar2(1) NOT NULL, is_dvd varchar2(1) NOT NULL, wholesale_price number(19,4) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE rentals ( id number(10,0) NOT NULL, customer_id number(5,0) NOT NULL, inventory_id number(10,0) NOT NULL, rental_date date NOT NULL, return_due_date date NOT NULL, is_rented varchar2(1) NOT NULL, is_returned varchar2(1) NOT NULL, total_charge number(19,4) NOT NULL, tax number(19,4) NOT NULL, PRIMARY KEY (id) ); /* Create Foreign Keys */ ALTER TABLE academy_awards_movies ADD FOREIGN KEY (academy_award_id) REFERENCES academy_awards (id) ; ALTER TABLE actors_movies ADD FOREIGN KEY (actor_id) REFERENCES actors (id) ; ALTER TABLE charges_rentals ADD FOREIGN KEY (charge_id) REFERENCES charges (id) ; ALTER TABLE rentals ADD FOREIGN KEY (customer_id) REFERENCES customers (id) ; ALTER TABLE directors_movies ADD FOREIGN KEY (director_id) REFERENCES directors (id) ; ALTER TABLE movies ADD FOREIGN KEY (distributor_id) REFERENCES distributors (id) ; ALTER TABLE rentals ADD FOREIGN KEY (inventory_id) REFERENCES inventories (id) ; ALTER TABLE directors_movies ADD FOREIGN KEY (movie_id) REFERENCES movies (id) ; ALTER TABLE actors_movies ADD FOREIGN KEY (movie_id) REFERENCES movies (id) ; ALTER TABLE academy_awards_movies ADD FOREIGN KEY (movie_id) REFERENCES movies (id) ; ALTER TABLE inventories ADD FOREIGN KEY (movie_id) REFERENCES movies (id) ; ALTER TABLE charges_rentals ADD FOREIGN KEY (rental_id) REFERENCES rentals (id) ; /* Create Triggers */ CREATE OR REPLACE TRIGGER TRI_academy_awards_id BEFORE INSERT ON academy_awards FOR EACH ROW BEGIN SELECT SEQ_academy_awards_id.nextval INTO :new.id FROM dual; END; / CREATE OR REPLACE TRIGGER TRI_academy_awards_movies_id BEFORE INSERT ON academy_awards_movies FOR EACH ROW BEGIN SELECT SEQ_academy_awards_movies_id.nextval INTO :new.id FROM dual; END; / CREATE OR REPLACE TRIGGER TRI_actors_id BEFORE INSERT ON actors FOR EACH ROW BEGIN SELECT SEQ_actors_id.nextval INTO :new.id FROM dual; END; / CREATE OR REPLACE TRIGGER TRI_actors_movies_id BEFORE INSERT ON actors_movies FOR EACH ROW BEGIN SELECT SEQ_actors_movies_id.nextval INTO :new.id FROM dual; END; / CREATE OR REPLACE TRIGGER TRI_charges_id BEFORE INSERT ON charges FOR EACH ROW BEGIN SELECT SEQ_charges_id.nextval INTO :new.id FROM dual; END; / CREATE OR REPLACE TRIGGER TRI_charges_rentals_id BEFORE INSERT ON charges_rentals FOR EACH ROW BEGIN SELECT SEQ_charges_rentals_id.nextval INTO :new.id FROM dual; END; / CREATE OR REPLACE TRIGGER TRI_customers_id BEFORE INSERT ON customers FOR EACH ROW BEGIN SELECT SEQ_customers_id.nextval INTO :new.id FROM dual; END; / CREATE OR REPLACE TRIGGER TRI_directors_id BEFORE INSERT ON directors FOR EACH ROW BEGIN SELECT SEQ_directors_id.nextval INTO :new.id FROM dual; END; / CREATE OR REPLACE TRIGGER TRI_directors_movies_id BEFORE INSERT ON directors_movies FOR EACH ROW BEGIN SELECT SEQ_directors_movies_id.nextval INTO :new.id FROM dual; END; / CREATE OR REPLACE TRIGGER TRI_distributors_id BEFORE INSERT ON distributors FOR EACH ROW BEGIN SELECT SEQ_distributors_id.nextval INTO :new.id FROM dual; END; / CREATE OR REPLACE TRIGGER TRI_inventories_id BEFORE INSERT ON inventories FOR EACH ROW BEGIN SELECT SEQ_inventories_id.nextval INTO :new.id FROM dual; END; / CREATE OR REPLACE TRIGGER TRI_movies_id BEFORE INSERT ON movies FOR EACH ROW BEGIN SELECT SEQ_movies_id.nextval INTO :new.id FROM dual; END; / CREATE OR REPLACE TRIGGER TRI_rentals_id BEFORE INSERT ON rentals FOR EACH ROW BEGIN SELECT SEQ_rentals_id.nextval INTO :new.id FROM dual; END; / /* Comments */ COMMENT ON COLUMN charges.charge_type IS 'standard_charge late_fee damaged_fee failure_to_rewind_fee other'; COMMENT ON COLUMN movies.movie_type IS 'suspense horror mystery comedy other';
CREATE SEQUENCE succeeded. CREATE SEQUENCE succeeded. CREATE SEQUENCE succeeded. CREATE SEQUENCE succeeded. CREATE SEQUENCE succeeded. CREATE SEQUENCE succeeded. CREATE SEQUENCE succeeded. CREATE SEQUENCE succeeded. CREATE SEQUENCE succeeded. CREATE SEQUENCE succeeded. CREATE SEQUENCE succeeded. CREATE SEQUENCE succeeded. CREATE SEQUENCE succeeded. CREATE TABLE succeeded. CREATE TABLE succeeded. CREATE TABLE succeeded. CREATE TABLE succeeded. CREATE TABLE succeeded. CREATE TABLE succeeded. CREATE TABLE succeeded. CREATE TABLE succeeded. CREATE TABLE succeeded. CREATE TABLE succeeded. CREATE TABLE succeeded. CREATE TABLE succeeded. CREATE TABLE succeeded. ALTER TABLE academy_awards_movies succeeded. ALTER TABLE actors_movies succeeded. ALTER TABLE charges_rentals succeeded. ALTER TABLE rentals succeeded. ALTER TABLE directors_movies succeeded. ALTER TABLE movies succeeded. ALTER TABLE rentals succeeded. ALTER TABLE directors_movies succeeded. ALTER TABLE actors_movies succeeded. ALTER TABLE academy_awards_movies succeeded. ALTER TABLE inventories succeeded. ALTER TABLE charges_rentals succeeded. TRIGGER TRI_academy_awards_id Compiled. TRIGGER TRI_academy_awards_movies_id Compiled. TRIGGER TRI_actors_id Compiled. TRIGGER TRI_actors_movies_id Compiled. TRIGGER TRI_charges_id Compiled. TRIGGER TRI_charges_rentals_id Compiled. TRIGGER TRI_customers_id Compiled. TRIGGER TRI_directors_id Compiled. TRIGGER TRI_directors_movies_id Compiled. TRIGGER TRI_distributors_id Compiled. TRIGGER TRI_inventories_id Compiled. TRIGGER TRI_movies_id Compiled. TRIGGER TRI_rentals_id Compiled. COMMENT ON succeeded. COMMENT ON succeeded.
/* You will get an error: "ORA-01400" because the customers.first_name is not allowed to insert NULL into. */
INSERT INTO customers (id, first_name, middle_name, last_name, street_address, city, state, zip_code, phone_number) VALUES ('1', '', 'D.', 'Smith', '123 Cherry Street', 'San Jose', 'CA', '95124', '+1-408-123-4567');
/* You will get an error: "ORA-02292: integrity constraint" because the customers table is referenced from the rentals table as a foreign key. */
delete from customers where id = 5;
Error starting at line 2 in command:
INSERT INTO customers (id, first_name, middle_name, last_name, street_address, city, state, zip_code, phone_number) VALUES ('1', '', 'D.', 'Smith', '123 Cherry Street', 'San Jose', 'CA', '95124', '+1-408-123-4567')
Error report:
SQL Error: ORA-01400: cannot insert NULL into ("CM320B11"."CUSTOMERS"."FIRST_NAME")
01400. 00000 - "cannot insert NULL into (%s)"
*Cause:
*Action:
Error starting at line 5 in command:
delete from customers where id = 5
Error report:
SQL Error: ORA-02292: integrity constraint (CM320B11.SYS_C00451237) violated - child record found
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause: attempted to delete a parent key value that had a foreign
dependency.
*Action: delete dependencies first then parent or disable constraint.
-- customers
INSERT INTO customers (id, first_name, middle_name, last_name, street_address, city, state, zip_code, phone_number) VALUES ('1', 'John', 'D.', 'Smith', '123 Cherry Street', 'San Jose', 'CA', '95124', '+1-408-123-4567');
INSERT INTO customers (id, first_name, middle_name, last_name, street_address, city, state, zip_code, phone_number) VALUES ('2', 'Jane', '', 'Smith', '456 Blossom Avenue', 'Santa Clara', 'CA', '950506709', '+1-408-777-6666');
INSERT INTO customers (id, first_name, middle_name, last_name, street_address, city, state, zip_code, phone_number) VALUES ('3', 'Myke', 'H.', 'Johnson', '789 Flower Street', 'New York', 'NY', '10007', '+1-646-222-3333');
INSERT INTO customers (id, first_name, middle_name, last_name, street_address, city, state, zip_code, phone_number) VALUES ('4', 'Anne', '', 'Miller', '777 Dream Boulevard', 'Baltimore', 'MD', '21093', '+1-443-567-8899');
INSERT INTO customers (id, first_name, middle_name, last_name, street_address, city, state, zip_code, phone_number) VALUES ('5', 'Bob', '', 'Brown', '999 Hapiness Avenue', 'Seattle', 'WA', '98103', '+1-206-213-7777');
-- charges
INSERT INTO charges (id, charge_type, charge) VALUES ('1', 'standard_charges', '2.99');
INSERT INTO charges (id, charge_type, charge) VALUES ('2', 'late_fee', '0.99');
INSERT INTO charges (id, charge_type, charge) VALUES ('3', 'damaged_fee', '9.99');
INSERT INTO charges (id, charge_type, charge) VALUES ('4', 'failure_to_rewind_fee', '4.99');
-- distributors
INSERT INTO distributors (id, name, shipment_quantity) VALUES ('1', 'A Corporation', '500');
INSERT INTO distributors (id, name, shipment_quantity) VALUES ('2', 'B Inc.', '250');
INSERT INTO distributors (id, name, shipment_quantity) VALUES ('3', 'C LLC.', '150');
-- movies
INSERT INTO movies (id, distributor_id, title, movie_type, running_length, rating, year_released, is_video, is_dvd, wholesale_price) VALUES ('1', '3', 'American Sniper', 'suspense', '100', '8', '2014', 't', 'f', '20.99');
INSERT INTO movies (id, distributor_id, title, movie_type, running_length, rating, year_released, is_video, is_dvd, wholesale_price) VALUES ('2', '2', 'Boyhood', 'horror', '120', '7', '2014', 'f', 't', '21.99');
INSERT INTO movies (id, distributor_id, title, movie_type, running_length, rating, year_released, is_video, is_dvd, wholesale_price) VALUES ('3', '1', 'Foxcatcher', 'mystery', '95', '7', '2014', 'f', 't', '23.99');
INSERT INTO movies (id, distributor_id, title, movie_type, running_length, rating, year_released, is_video, is_dvd, wholesale_price) VALUES ('4', '1', 'The Grand Budapest Hotel', 'comedy', '140', '9', '2014', 't', 'f', '24.99');
INSERT INTO movies (id, distributor_id, title, movie_type, running_length, rating, year_released, is_video, is_dvd, wholesale_price) VALUES ('5', '1', 'The Imitation Game', 'other', '125', '6', '2014', 'f', 't', '19.99');
INSERT INTO movies (id, distributor_id, title, movie_type, running_length, rating, year_released, is_video, is_dvd, wholesale_price) VALUES ('6', '2', 'Birdman', 'other', '130', '10', '2014', 'f', 't', '26.99');
-- inventories
INSERT INTO inventories (id, movie_id) VALUES ('1', '1');
INSERT INTO inventories (id, movie_id) VALUES ('2', '1');
INSERT INTO inventories (id, movie_id) VALUES ('3', '1');
INSERT INTO inventories (id, movie_id) VALUES ('4', '2');
INSERT INTO inventories (id, movie_id) VALUES ('5', '2');
INSERT INTO inventories (id, movie_id) VALUES ('6', '2');
INSERT INTO inventories (id, movie_id) VALUES ('7', '2');
INSERT INTO inventories (id, movie_id) VALUES ('8', '3');
INSERT INTO inventories (id, movie_id) VALUES ('9', '3');
INSERT INTO inventories (id, movie_id) VALUES ('10', '3');
INSERT INTO inventories (id, movie_id) VALUES ('11', '3');
INSERT INTO inventories (id, movie_id) VALUES ('12', '4');
INSERT INTO inventories (id, movie_id) VALUES ('13', '4');
INSERT INTO inventories (id, movie_id) VALUES ('14', '4');
INSERT INTO inventories (id, movie_id) VALUES ('15', '5');
INSERT INTO inventories (id, movie_id) VALUES ('16', '5');
INSERT INTO inventories (id, movie_id) VALUES ('17', '6');
INSERT INTO inventories (id, movie_id) VALUES ('18', '6');
INSERT INTO inventories (id, movie_id) VALUES ('19', '6');
INSERT INTO inventories (id, movie_id) VALUES ('20', '6');
INSERT INTO inventories (id, movie_id) VALUES ('21', '6');
INSERT INTO inventories (id, movie_id) VALUES ('22', '6');
INSERT INTO inventories (id, movie_id) VALUES ('23', '6');
INSERT INTO inventories (id, movie_id) VALUES ('24', '6');
-- rentals
INSERT INTO rentals (id, inventory_id, customer_id, rental_date, return_due_date, is_rented, is_returned, total_charge, tax) VALUES ('1', '1', '1', TO_DATE('6/23/2015','MM/DD/YYYY'), TO_DATE('6/30/2015','MM/DD/YYYY'), 'f', 't', '2.99', '0.3');
INSERT INTO rentals (id, inventory_id, customer_id, rental_date, return_due_date, is_rented, is_returned, total_charge, tax) VALUES ('2', '4', '2', TO_DATE('6/25/2015','MM/DD/YYYY'), TO_DATE('7/2/2015','MM/DD/YYYY'), 't', 'f', '2.99', '0.3');
INSERT INTO rentals (id, inventory_id, customer_id, rental_date, return_due_date, is_rented, is_returned, total_charge, tax) VALUES ('3', '4', '2', TO_DATE('6/25/2015','MM/DD/YYYY'), TO_DATE('7/2/2015','MM/DD/YYYY'), 't', 'f', '0.99', '0.1');
INSERT INTO rentals (id, inventory_id, customer_id, rental_date, return_due_date, is_rented, is_returned, total_charge, tax) VALUES ('4', '9', '4', TO_DATE('6/25/2015','MM/DD/YYYY'), TO_DATE('7/2/2015','MM/DD/YYYY'), 'f', 't', '2.99', '0.3');
INSERT INTO rentals (id, inventory_id, customer_id, rental_date, return_due_date, is_rented, is_returned, total_charge, tax) VALUES ('5', '13', '5', TO_DATE('6/26/2015','MM/DD/YYYY'), TO_DATE('7/3/2015','MM/DD/YYYY'), 't', 'f', '2.99', '0.3');
INSERT INTO rentals (id, inventory_id, customer_id, rental_date, return_due_date, is_rented, is_returned, total_charge, tax) VALUES ('6', '13', '5', TO_DATE('6/26/2015','MM/DD/YYYY'), TO_DATE('7/3/2015','MM/DD/YYYY'), 't', 'f', '0.99', '0.1');
INSERT INTO rentals (id, inventory_id, customer_id, rental_date, return_due_date, is_rented, is_returned, total_charge, tax) VALUES ('7', '16', '2', TO_DATE('6/26/2015','MM/DD/YYYY'), TO_DATE('7/3/2015','MM/DD/YYYY'), 'f', 't', '2.99', '0.3');
INSERT INTO rentals (id, inventory_id, customer_id, rental_date, return_due_date, is_rented, is_returned, total_charge, tax) VALUES ('8', '17', '3', TO_DATE('6/27/2015','MM/DD/YYYY'), TO_DATE('7/4/2015','MM/DD/YYYY'), 't', 'f', '2.99', '0.3');
INSERT INTO rentals (id, inventory_id, customer_id, rental_date, return_due_date, is_rented, is_returned, total_charge, tax) VALUES ('9', '17', '3', TO_DATE('6/27/2015','MM/DD/YYYY'), TO_DATE('7/4/2015','MM/DD/YYYY'), 't', 'f', '0.99', '0.1');
INSERT INTO rentals (id, inventory_id, customer_id, rental_date, return_due_date, is_rented, is_returned, total_charge, tax) VALUES ('10', '21', '5', TO_DATE('6/29/2015','MM/DD/YYYY'), TO_DATE('7/6/2015','MM/DD/YYYY'), 't', 'f', '2.99', '0.3');
INSERT INTO rentals (id, inventory_id, customer_id, rental_date, return_due_date, is_rented, is_returned, total_charge, tax) VALUES ('11', '22', '1', TO_DATE('6/30/2015','MM/DD/YYYY'), TO_DATE('7/7/2015','MM/DD/YYYY'), 't', 'g', '2.99', '0.3');
-- charges_rentals
INSERT INTO charges_rentals (id, rental_id, charge_id) VALUES ('1', '1', '1');
INSERT INTO charges_rentals (id, rental_id, charge_id) VALUES ('2', '2', '1');
INSERT INTO charges_rentals (id, rental_id, charge_id) VALUES ('3', '3', '2');
INSERT INTO charges_rentals (id, rental_id, charge_id) VALUES ('4', '4', '1');
INSERT INTO charges_rentals (id, rental_id, charge_id) VALUES ('5', '5', '1');
INSERT INTO charges_rentals (id, rental_id, charge_id) VALUES ('6', '6', '2');
INSERT INTO charges_rentals (id, rental_id, charge_id) VALUES ('7', '7', '1');
INSERT INTO charges_rentals (id, rental_id, charge_id) VALUES ('8', '8', '1');
INSERT INTO charges_rentals (id, rental_id, charge_id) VALUES ('9', '9', '2');
INSERT INTO charges_rentals (id, rental_id, charge_id) VALUES ('10', '10', '1');
INSERT INTO charges_rentals (id, rental_id, charge_id) VALUES ('11', '11', '1');
-- actors
INSERT INTO actors (id, first_name, middle_name, last_name) VALUES ('1', 'Bradley', '', 'Cooper');
INSERT INTO actors (id, first_name, middle_name, last_name) VALUES ('2', 'Benedict', '', 'Cumberbatch');
INSERT INTO actors (id, first_name, middle_name, last_name) VALUES ('3', 'Ethan', '', 'Hawke');
INSERT INTO actors (id, first_name, middle_name, last_name) VALUES ('4', 'Mark', '', 'Ruffalo');
INSERT INTO actors (id, first_name, middle_name, last_name) VALUES ('5', 'Ralph', '', 'Fiennes');
INSERT INTO actors (id, first_name, middle_name, last_name) VALUES ('6', 'Michael', '', 'Keaton');
-- actors_movies
INSERT INTO actors_movies (id, actor_id, movie_id) VALUES ('1', '1', '1');
INSERT INTO actors_movies (id, actor_id, movie_id) VALUES ('2', '2', '5');
INSERT INTO actors_movies (id, actor_id, movie_id) VALUES ('3', '3', '2');
INSERT INTO actors_movies (id, actor_id, movie_id) VALUES ('4', '4', '3');
INSERT INTO actors_movies (id, actor_id, movie_id) VALUES ('5', '5', '4');
INSERT INTO actors_movies (id, actor_id, movie_id) VALUES ('6', '6', '6');
-- directors
INSERT INTO directors (id, first_name, middle_name, last_name) VALUES ('1', 'Clint', '', 'Eastwood');
INSERT INTO directors (id, first_name, middle_name, last_name) VALUES ('2', 'Richard', '', 'Linklater');
INSERT INTO directors (id, first_name, middle_name, last_name) VALUES ('3', 'Bennett', '', 'Miller');
INSERT INTO directors (id, first_name, middle_name, last_name) VALUES ('4', 'Wes', '', 'Anderson');
INSERT INTO directors (id, first_name, middle_name, last_name) VALUES ('5', 'Morten', '', 'Tyldum');
INSERT INTO directors (id, first_name, middle_name, last_name) VALUES ('6', 'Alehandro', 'G.', 'Inarritu');
-- directors_movies
INSERT INTO directors_movies (id, director_id, movie_id) VALUES ('1', '1', '1');
INSERT INTO directors_movies (id, director_id, movie_id) VALUES ('2', '2', '2');
INSERT INTO directors_movies (id, director_id, movie_id) VALUES ('3', '3', '3');
INSERT INTO directors_movies (id, director_id, movie_id) VALUES ('4', '4', '4');
INSERT INTO directors_movies (id, director_id, movie_id) VALUES ('5', '5', '5');
INSERT INTO directors_movies (id, director_id, movie_id) VALUES ('6', '6', '6');
-- academy_awards
INSERT INTO academy_awards (id, category, year_awarded, is_winner, is_nominee) VALUES ('1', 'Best Picture', '2015', 't', 'f');
INSERT INTO academy_awards (id, category, year_awarded, is_winner, is_nominee) VALUES ('2', 'Best Picture', '2015', 'f', 't');
INSERT INTO academy_awards (id, category, year_awarded, is_winner, is_nominee) VALUES ('3', 'Best Directing', '2015', 't', 'f');
INSERT INTO academy_awards (id, category, year_awarded, is_winner, is_nominee) VALUES ('4', 'Best Directing', '2015', 'f', 't');
INSERT INTO academy_awards (id, category, year_awarded, is_winner, is_nominee) VALUES ('5', 'Best Actor', '2015', 'f', 't');
INSERT INTO academy_awards (id, category, year_awarded, is_winner, is_nominee) VALUES ('6', 'Best Supporting Actor', '2015', 'f', 't');
-- academy_awards_movies
INSERT INTO academy_awards_movies (id, academy_award_id, movie_id) VALUES ('1', '2', '1');
INSERT INTO academy_awards_movies (id, academy_award_id, movie_id) VALUES ('2', '5', '1');
INSERT INTO academy_awards_movies (id, academy_award_id, movie_id) VALUES ('3', '2', '2');
INSERT INTO academy_awards_movies (id, academy_award_id, movie_id) VALUES ('4', '4', '2');
INSERT INTO academy_awards_movies (id, academy_award_id, movie_id) VALUES ('5', '6', '2');
INSERT INTO academy_awards_movies (id, academy_award_id, movie_id) VALUES ('6', '4', '3');
INSERT INTO academy_awards_movies (id, academy_award_id, movie_id) VALUES ('7', '5', '3');
INSERT INTO academy_awards_movies (id, academy_award_id, movie_id) VALUES ('8', '4', '4');
INSERT INTO academy_awards_movies (id, academy_award_id, movie_id) VALUES ('9', '2', '5');
INSERT INTO academy_awards_movies (id, academy_award_id, movie_id) VALUES ('10', '4', '5');
INSERT INTO academy_awards_movies (id, academy_award_id, movie_id) VALUES ('11', '5', '5');
INSERT INTO academy_awards_movies (id, academy_award_id, movie_id) VALUES ('12', '1', '6');
INSERT INTO academy_awards_movies (id, academy_award_id, movie_id) VALUES ('13', '3', '6');
INSERT INTO academy_awards_movies (id, academy_award_id, movie_id) VALUES ('14', '5', '6');
INSERT INTO academy_awards_movies (id, academy_award_id, movie_id) VALUES ('15', '6', '6');
1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted 1 rows inserted
select id, first_name, middle_name, last_name, street_address, zip_code from customers order by id; select * from rentals where rental_date >= (sysdate-30) order by rental_date; select * from distributors order by name; update customers set last_name = 'Jones' where id = 4;
ID FIRST_NAME MIDDLE_NAME LAST_NAME STREET_ADDRESS ZIP_CODE ---------------------- --------------- --------------- --------------- ------------------------------ ---------------------- 1 John D. Smith 123 Cherry Street 95124 2 Jane Smith 456 Blossom Avenue 950506709 3 Myke H. Johnson 789 Flower Street 10007 4 Anne Miller 777 Dream Boulevard 21093 5 Bob Brown 999 Hapiness Avenue 98103 5 rows selected ID CUSTOMER_ID INVENTORY_ID RENTAL_DATE RETURN_DUE_DATE IS_RENTED IS_RETURNED TOTAL_CHARGE TAX ---------------------- ---------------------- ---------------------- ------------------------- ------------------------- --------- ----------- ---------------------- ---------------------- 1 1 1 23-JUN-15 30-JUN-15 f t 2.99 0.3 2 2 4 25-JUN-15 02-JUL-15 t f 2.99 0.3 3 2 4 25-JUN-15 02-JUL-15 t f 0.99 0.1 4 4 9 25-JUN-15 02-JUL-15 f t 2.99 0.3 5 5 13 26-JUN-15 03-JUL-15 t f 2.99 0.3 6 5 13 26-JUN-15 03-JUL-15 t f 0.99 0.1 7 2 16 26-JUN-15 03-JUL-15 f t 2.99 0.3 8 3 17 27-JUN-15 04-JUL-15 t f 2.99 0.3 9 3 17 27-JUN-15 04-JUL-15 t f 0.99 0.1 10 5 21 29-JUN-15 06-JUL-15 t f 2.99 0.3 11 1 22 30-JUN-15 07-JUL-15 t g 2.99 0.3 11 rows selected ID NAME SHIPMENT_QUANTITY ---------------------- ---------------------------------------- ---------------------- 1 A Corporation 500 2 B Inc. 250 3 C LLC. 150 3 rows selected 1 rows updated
Use SQL to Create a table with at least 4 attributes one of which is the Primary key.
Then, insert 2 records into the table.
Finally, use a select statement to show the content of your table after the inserts.
Be sure your SQL statements work without issue and show each of of your statements.
I created a students table as shown in the ERD below and inserted two records into it.
/* Creates the students table. */
CREATE TABLE students (
student_id number(5,0) NOT NULL UNIQUE,
last_name char NOT NULL,
first_name char NOT NULL,
middle_name char,
enrolled_at date NOT NULL,
finished_at date,
active_status varchar2(1) NOT NULL,
date_of_birthday date NOT NULL,
gender varchar2(1) NOT NULL,
address char NOT NULL,
city char NOT NULL,
state char NOT NULL,
zip_code number(10,0) NOT NULL,
PRIMARY KEY (student_id)
);
/* Inserts two records into the students table. */
INSERT ALL
INTO students (
last_name,
first_name,
enrolled_at date,
active_status,
gender,
date_of_birthday,
address,
city,
state,
zip_code
) values (
'Smith',
'John',
'7/1/2015',
't',
'm',
'4/1/1996',
'123 Cherry Stree',
'San Jose',
'CA'
95124
)
INTO students (
last_name,
first_name,
enrolled_at date,
active_status,
gender,
date_of_birthday,
address,
city,
state,
zip_code
) values (
'Smith',
'Jane',
'7/1/2015',
't',
'f',
'3/1/1996',
'456 Blossom Avenue',
'Santa Clara',
'CA'
950506709
)
SELECT * FROM dual;
/* Retrieves all the records of the students table. */
SELECT *
FROM students;
For your final discussion topic, repond to at least one of the following topics.
1) In reviewing current help wanted advertising in the papers, online, word of mouth, and other sources, what do you feel are the most desirable RDBMSs to have experience with for a career as a DBA or database developer and what skill sets are required? Why?
2) What is PL/SQL and how can that be used in DBA and database development work?
3) What are stored procedures and triggers and how are they used in database applications?
4) What trends in Database technology seem to be most prominent? Be sure to explain the trend and reference your sources.
2) What is PL/SQL and how can that be used in DBA and database development work?
3) What are stored procedures and triggers and how are they used in database applications?
A stored procedure is a group of SQL statements. Most of the popular RDBMs including Oracle Database, Microsoft SQL Server, MySQL, and PortgreSQL support stored procedures, and the major benefits of using a stored procedure because those SQL statements are precompiled before the first time execution (Chapple, M., n.d.).
PL/SQL is a stored procedure for Oracle Database, which is a procedural extention of SQL. Basically, you can code conditional expressions and SQL statements between block statements such as DECLARE, BEGIN, EXCEPTION, and END; in addition, by using database triggers, you can call a particular PL/SQL block when an event such as INSERT, UPDATE, and DELTE is occurred in a specific table (Hall, T., n.d.). Actually, all PL/SQL source and compiled code is stored in the data dictionary (Owens, K., 2003).
Instructions
Write a two-page executive summary for your boss explaining how a relational data solution can be applied to a current business problem or area for improvement. Assume that your boss knows nothing about relational database theory. The goal of this summary is to obtain your boss's approval to proceed with your stated project. Do not focus on technical aspects of a database management system. Focus on how the information will be captured, manipulated, managed, and shared, and the value the database brings to the organization. Include brief examples of how other industries (both domestic and international) have successfully used relational databases to increase efficiency.
Your paper must follow all APA Style Guide requirements. All sources must be properly cited using APA guidelines. Include an APA title page and an APA References page. Thes title page and References pages are in addition to the minimum of 2 pages of body text for your executive summary. In your summary you must include an introduction section with a heading, body text topic sections as apprporiate with headings, and a conclusion section with a heading. Note that APA requires 12 point Times Roman font throughout and 1" margins.
Submissions:
1. You should submit your well-organized Word document in the WebTycho portfolio in the Short Paper area before the due date.
2. You should name your paper Yourname_shortpaper.docx.
