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 […]
Archive for the ‘Database design and optimisation’ Category
Posted in 
Scenario Activity
September 7th, 2020 
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 […]
Transaction control procedure
September 1st, 2020 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 […]
GIS Database Management Assignment 1
August 19th, 2020 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 […]
SQL Stored Procedures
August 7th, 2020 Create a stored procedure NEWGUESTBOOKING that adds a booking for a new guest. The stored procedure NEWGUESTBOOKING receives the following parameters Input ParametersGuest nameGuest addressHotelNoRoomNodateFromdateTo Output ParametersBookingNo intBookingResult char(40) The stored procedure1. Set autocommit = OFF;2. Issue the START TRANSACTION;3. Get the max guestNo in the database.4. Insert a new guest into the guest table […]
Pick A pet
July 13th, 2020 7.1 Output Design Prototypes 7.1.1 Design and implement (with data that is important to the management) 3 reports for management, 1 detailed, 1 exception, and 1 summary (one by each student). Each report design description & layout:Title- (Pick a Pet)Purpose What need does it fulfillData Fields list from the databaseCalculated fieldsGrouping/Sorted (for detailed report) or […]
Design a database in MS-Access(Time line Plan)
July 8th, 2020 PART I Design a database in MS-Access(Time line Plan) Ex) schedule, HR Time line1. Describe your business environment and why you need a database.2. Draw your business E-R Model.3. Create a MS-Access database that reflects to your business needs with:a. Entities Relationship [Database Tools > Relationships]b. At least 4 tablesc. At least 3 queriesd. At […]
SQL – Creating Views
July 7th, 2020 In this series, we will build and enhance the a hotel booking database. Series 1 Objectives – Create Views Create the bookingdb environment by downloading the bookingdb.zip file attached in this Assignment. Review the base tables that is created. Code SQL to create a VIEW for each base table. The viewname should begin with ‘v’+basetablename. […]
BigQuery Public Data
July 7th, 2020 Student Learning ObjectivesAfter successful completion of this assignment the student will be able to: Use BigQuery to query public datasets.Write queries using aggregate functions, grouping, ordering, and subqueries.Important Notes about GradingA reminder, any syntax error, including missing semi-colon at the end of even one SQL statement, will result in a grade of 0 for the […]
SQL Workbench Work
July 7th, 2020 In this assignment, we will create a view in finhistdb to pick S&P 100 tickers. S&P100 is an index of ticker symbols that comprise the index. Create a View named currentSP100Price and include all the Tickers and current prices of the stocks. The base table to use is pricehist table. Since there are 100 tickers, […]
