![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
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.
