After reviewing Appendix C of Hands-On Database, use Visio to re-create one of the E-R diagrams provided in Appendix F of Database Design for Mere Mortals. Please note that the symbols used in Visio are not identical to those used in Database Design for Mere Mortals. You should create your diagram using symbols with similar meanings.
I will provide the reading material and access to Visio if you do not have access to it
Vince Roberts runs a vintage record shop in the University district. His shop sells 45s, LPs, and even old 76 RPM records. Most of his stock is usedhe buys used vinyl from customers or finds them at yard sales and discount storesbut he does sell new albums that are released on vinyl. For a couple of years, he has kept most of his inventory either in his head or in a spiral notebook he keeps behind the sale counter. But his inventory and his business have grown to where that is far from sufficient.
Vince is looking for someone to make him a database. He knows he needs to get a better handle on several aspects of his business: He needs to know the extent and condition of his inventory. He needs to know the relative value of his inventorysome records are worth a fortune; some are nearly worthless. He also needs to track where, from whom, and for how much he purchased his stock. He needs to track his sales. He often is not entirely sure how much money he has spent or how much money he has earned.
In addition he would like to allow customers to make specific requests and notify them if a requested item comes in. More generally he would like to make an email list of interested customers in order to let them know about new items of interest.
Someday, he would like to expand his business online. But he knows he needs to have everything under control before then.
******ASSIGNMENT******
List the major topics for this database.
Write a draft statement of work. Include a brief history, a statement of scope, objectives, and a preliminary timeline.
Documentation: Start a notebook, either electronically or physically, to record your progress with the scenario database. Add the statement of work and any notes to the notebook.
Transaction control***
Create a stored procedure with the following requirements:
Accept input that identifies the customer, the ISBN for one book, and the quantity being ordered. CHALLENGE — try this for two books instead of one
Create an ORDER record that reflects today’s date as the OrderDate.
Create an OrderItem record for the book(s) being ordered, reduce the (quantity on hand) appropriately for the ordered book, and either:
Return an error message if there is not sufficient inventory for the book ordered.
OR
Commit the transaction
Leave the shipping information null (there will be a separate process for shipping information).
Remember that the Order# is a unique, sequential key.
I only need questions 6 & 9 finished. As part of question 4, I used FME workbench to import the tables needed for question 6 & 9 (tblcity & tblstate) into Oracle SQL Developer. I’m supposed to do questions 6 & 9 in Oracle SQL Developer. So as long as I can copy & paste the code into Oracle SQL Developer later I will be fine!
Question 6: Write a PL/SQL stored procedure that receives the keyword ALL or the name of a city as a parameter. When the keyword ALL is received, the procedure should display, with descriptive words, the proportion of the state population that lives in each city. When the name of a city is received, the procedure should display, a) the proportion of the state population that lives in the city, and b) the states population density. Use a single SELECT statement which joins the tables together either using a table join or a WHERE clause. Include an exception section for when no records are returned, too many records are returned or when the state population is zero (division by zero).
Include a driving procedure (anonymous program block) that obtains the name of a city or the keyword ALL from the user and runs your stored procedure using the value obtained.
Question 9: Create a table DBMonitor to audit insertion, update and deletion operations on the tblCity table. DBMonitor has the following columns: transaction date, user performing the transaction, transaction type, old value, new value, table name, transaction type number.
Write a PL/SQL stored function that receives the transaction type (insert, update or delete) as a parameter and returns the number of transactions in the DBMonitor table of the transaction type received.
When a record is inserted or updated or deleted, insert a record into the DBMonitor table. The record must record information for all of the columns in the DBMonitor table: transaction date, user performing the transaction, transaction type, old value, new value, table name and transaction type number. The transaction type number is the number of transactions in the DBMonitor table of the type that caused the trigger to run. For example, if the transaction that caused the trigger to run is the fourth insert transaction, then the transaction type number should be 4. Use the stored function to obtain the number of transactions in the DBMonitor table of the type that caused the trigger to run.
For the two old and new value columns in DBMonitor, make sure that you record all the column names with their old or new values. Dont create a separate old and new value column in DBMonitor for each column.
Only one row is inserted into DBMonitor for each affected row when the trigger fires, no matter how many columns are modified.
Hint: A useful keyword for this question is: USER
Include a driving procedure (anonymous program block) that tests insertion, update and a deletion operations on the tblCity table.
Must develop and ERD diagram for an Online retail business of choice. The retail business must be able to collect CC payments. At minimum the developed relational database must collect and store the following information;
Stock
Customer Details
Orders
Suppliers
Payments
Returns
Using the ERD diagram that was developed earlier in Part A now expand upon the original ERD and develop and populate a real life Relational Database to hold their business data and ALSO a Data Mart to support the reporting requirements of the retail company.
In order to implement this Data Mart students are required to automate the loading of the data from the live production database to the Data Mart. This should be done by scripting.
Project Report Document
a. Background Information Document and relevant graphs connected via Excel to 2 reports of YOUR CHOICE
b. Script File to Create the Data Mart database and tables and the following reports
c. The Data Mart SHOULD ONLY CONTAIN results only from Views (Questions 1, 3 and 4 ONLY)
1. Create a View showing all transactions for a given week in your business.
2. Create a trigger that stores stock levels once a sale takes place.
3. Create a View of stock (by supplier) purchased by you.
4. Create a View of Total stock sold to general public (group by supplier).
5. Detail and total all transactions (SALES) for the month-to-date. (A Group By with Roll-Up)
6. Detail and total all SALES for the year-to-date. (A Group By with Roll-Up)
7. Detail & total transactions broken down on a monthly basis for 1 year. (A Group By with Roll-Up)
8. Display the growth in sales/services (as a percentage) for your business, from the 1st month of opening until now.
9. Use Excel to create a graphical representation of any 2 of the queries of YOUR CHOICE, for example Q1 & Q4. (Any type of graph)
10. Add both the SQL statements and graphs into a Word document report (Your SQL code and the screenshots and relevant graphs & ERD Diagram). The Word document must show the results of your queries when run (Screen shot of the Output).
A small university is in the process of creating its first database system, and you are the chief architect of this system. This university does not have majors, faculties, and departments, but it does have programs. A program usually has 5 courses (in real situation, a program usually has much more courses, but to make the test data preparation easier, we set this number as 5), For simplicity, all the courses in a program are required and fixed. Prerequisite is not considered.
This university runs in a two-semester system: every year has Winter semester and Fall semester. In every semester, a course may be offered once or not be offered. A course-offering entity is necessary. A professor may teach any courses, and will teach one or more courses each semester. A student may register in a few programs. A course is passed when the grade is 60 or more.
Every person in the campus, a student or a professor, has an ID. This ID is uniformly managed, that means all the IDs are from the same pool, there is no special digit can be used to distinguish professors or students.
(6 marks) Using MySQL Workbench to create an ER model of this new database.
(4 marks) Convert the model into SQL statements which create tables. All the tables must be normalized or denormalized
(5 marks) Create a view Transcript, which is the transcript of all students. It should contain the following columns: studentID, studentName, course, year, semester, grade, professorName. Insert a few data items into the tables. Prepare a select statement to show a students transcript.
(5 marks) Create a view: TeachingProfile, which shows the teaching history of all professors. It should contain the following columns: ProfessorID, ProfessorName, course, year, semester, enrollment. Insert a few data items into the tables. Prepare a select statement to show a professors teaching profile.
(5 marks) Create a view Audit, which is the audit of all students. It should contain the following columns: studentID, programID, neededCourse, (such a row indicates that this course has not been taken or has not passed.). Insert a few data items into the tables. Prepare a select statement to show a students audit.
The design of the tables/relationships should be intuitive, straightforward, and should support university activities. Item 3, 4, 5 are the university activities.
Draw an ER diagram using the IDEF1X Notation and ERWIN for the following two applications. If you discover that the following narrative is incomplete, make up reasonable explanations to complete the description. State clearly your assumptions. Your assumptions should not contradict the assumptions stated in the narrative. You may download a trail version of ERWIN at http://erwin.com/
1.The software maintains and manages information related to employees, rooms, committees, and committee meetings of a company. The information
include at least the following data:
Employee: IDNumber, Full Name, Office Location, Telephone Number, Department, and Position.
Employees Schedule: Date, Start Time, End Time, and Task .
Room: Building Number, Room Number, and Capacity.
Room Schedule: Date, Start Time, End Time, and Status.
Committee: Title, Chairs Name , ChairsIDNumber, and Members.
Committee Meeting: Meeting Number, Date, Start Time, and End Time.
2. A software product that records and tracks all your bank accounts. The main menu shows the following possible transactions:
NewAccount: Enter information of a new bank account: BankName, Account#;
DeleteAccount: Delete an account;
Deposit: Enter information of every deposit transaction using Account# or BankName: Date, Amount, amount of CashOut;
Withdraw: Enter the information of every withdraw transaction using Account# or BankName: Date , Amount;
EnterCheck: Enter information of each check you write: Check#, CheckDate, Amount, ToWhom, ForWhat;
EnterStatement: Enter the information of each bank statement you receive: StatementDate, StartDate, EndDate, Balance, and a single line for each,
Check cashed including: Check#, CheckDate, Amount, DateCashed;
Deposit: Date, Amount;
Withdraw: Date, Amount.
The project charter is already done, just uploaded it so you know how it started off as. The presentation slides from slide 37 to 40 are the only relevant ones ignore the rest. In slide 39 all you have to write about is “Detailed Communication Strategy” and “Consideration of the impact of GDPR” You don’t need to do the rest as my Team members are working on those parts
Step 1. Understand the specification of the problem
Read the following paragraph carefully. Multiple times, if necessary. Then follow the process we learned in Lab 2 to annotate the primary terms and actions.
Basketball is one of the most popular sports in the United States. Basketball tournaments consist of games between two teams at various stages (league, playoff, final, etc.). Each individual game in the tournament is played between two competing teams played at a specific city or stadium with a certain capacity, and attended by a number of spectators. The matches may also be televised on a TV channel, and viewed by an estimated number of TV viewers. During each match, the teams typically play two halves (which may be further divided in quarters depending on the tournament). Players score points, commit fouls and perform blocks, rebounds, and assists. Each match ends with a score with the team with the higher score being the winner. Players play for a team and have a position and a jersey number in addition to physical attributes like weight, height, birthday, etc. Players may switch teams from one season to the next. In our scenario, let’s assume that they do not switch teams mid-season. Each team has a support team with at least a coach, assistant coach, and physio.
Step 2. Identify Terms, actions and Business Rules
Start an empty Microsoft Word document, and put your name and PantherID at the top of the document. Now for Step 2 – first write down all the terms that you identify that you feel should be entities. Then write down all the actions between the terms. Then write down as many business rules that you can think of.
Step 3. Draw an ER model for the scenario.
Create an E-ER model for the above scenario using the diagramming tool of your choosing, but follow the constructs we used in the lab. Make sure you indicate all your strong entites, weak entities, supertypes and subtypes, cardinality and participation indicators, and primary keys for every strong entity. Make sure you come up with at least 3 attributes for each entity.
Copy and paste this diagram into your Word document.
Step 4. Relational Schema
Design the schema of the database from your E-R diagram. You can do this by just listing all the tables and attributes in the Word document. Indicate the primary keys with underlines and foreign keys with asterisks as we did in the lab.
Finally, write an SQL script with the CREATE TABLE, ALTER TABLE commands to create the complete schema of your design. Then create at least one row in each of the tables using insert statements. Your script should include the DROP, CREATE, ALTER and INSERT statements for creating all the tables and keys, followed by inserting at least one row in each table. You may need to insert more than one row in some tables. Save this script SEPARATELY in a text file (extension .txt). Do not include the DDL statements in your Word Document.
What to Submit
You will submit the following four documents:
The Word document containing your design, including the business rules, and all notes leading to your design. (.doc or .docx)
The original ER model in PDF format (.pdf)
The SQL script containing your schema creation and insertion commands (must not be in the Word document, and submitted as a .txt file)
Test your commands in livesql and make sure all commands run properly. Submit the transcript of running your script in LiveSQL in PDF form (in PDF – must show all tables created and rows inserted). (.pdf)
2. Design a database that will be used to store information for an IT Consulting Company that provides services for client organizations as well as purchases and resells equipment (servers, desktops, software, routers, etc.) to clients. Ensure that you diagram the
tables, columns / datatypes, and table relationships.
3. Create the database and tables from the previous step as well as populate the tables with at least 50 records (use a bulk import process). Also that the tables have the appropriate indexes and database options.
4. Create a SQL login and user that can be used to access and modify records within the database, as well as a separate SQL login that can only read records within the database. Use appropriate names for each one.
5. Ensure that you can isolate performance problems when they occur, as well as monitor ongoing SQL performance and usage. Optimize the performance of your database by performing the appropriate tasks. Ensure that your database is regularly checked for
inconsistencies and backed up. Any problems should be emailed to your trioscollege.com email address.
6. Create a new database that will be used as a data warehouse. Identify which information from your database is most valuable for business intelligence analysis. Next, define your dimensions for the data warehouse as well as the data points to store in the fact
table. Finally, select the columns to include in each dimension table, and optionally implement your design by creating the necessary SSIS packages.
7. Detach your databases and submit them to your instructor for marking.
