Category Archives: Database design and optimisation

Visio Assignment

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

Scenario Activity

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.


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 procedure

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. 


        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.

team project

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 

Assignment 1

Create a table with two columns. Name the table First Initial _ Last Name (e.g. John Dow
will create table j_dow). You have to audit all DML statements on your table. To do this you write two triggers:
1. To log any DML statements that users might run on this table. The results must be stored in the First Initial _ Last Name _ Log table (e.g. John Dow will create table j_dow_log).
The table should have unique event ID, values for both the Oracle and the system user who ran the query, the time it was executed and the type of DML query user ran.
2. To capture any data that was changed in the table. The results must be stored in the First Initial _ Last Name _ History table (e.g. John Dow will create table j_dow_history).
The table should reference the event ID from the log table, and store both old and new values for both columns.
What to submit: Name this file according to submission guidelines and include the following answers:
Code to create all three tables, constraints, insert statements. (10 points)

Code for the first trigger (10 points)

Test cases and expected results for the first trigger (30 points)

Code for the second trigger (10 points)

Test cases and expected results for the first trigger (30 points)

Designinga Relational Database

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)

SQL design and implement

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 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.

Database designer needed for creating an Entity Relationship Diagram (ERD)

Hi there,

I need todevelop an entity-relationship diagram (ERD) of songs on albums for popular music from 1950 to 2000. Please see the complete details of the project below:

Please note that this has to be done in the Stanford style of E/R modeling.

Thank you 🙂