代做BIS 324 Final Project – Spring 2024代做数据库编程

BIS 324 Final Project – Spring 2024

Table Create and Population due May 5

Business Problems and Queries due May 10

Introduction

You are to update your previous entity-relationship diagram correcting all errors (or use the ERD solution provided on CourseSite by the professor). You are to create the DDL, run it into your Oracle Express instance, populate the database tables and develop queries that would support some of the business functionality provided by the Northampton County Assessment site.  You are to essentially implement the ERD in the Oracle database.

Prior to doing this assignment you should research this information and understand its business model and possible current uses in real-life applications (such as sales, marketing, demographics, etc.)

Assignment Components

1) Updated ERD (optional). If you choose to use your own ERD, please update it based upon my comments from the previous grading and resubmit your ERD with this final project submission. Otherwise, if you do not submit a PDF File, I will assume you are using the professor’s solution.

How this is submitted: Via CourseSite Link

What is submitted: Data Modeler image (pdf) of relational model. (optional – no submission necessary if using professor ERD)

You may remove the land and tax_collector tables if  you would like. If you do so, you must add in a tax_collector_id field to the parcel.

2) Create database tables in your Oracle schema that correspond the ERD drawing. The table names MUST BE EXACTLY the same as the entity names on the ERD in order for me to find them for grading purposes. If the names are not the same or I cannot find the tables points will be taken off. All tables must have primary keys, proper data types, and not null designations as appropriate. Data Modeler will create foreign key constraints between the tables in order to establish data integrity for the foreign keys.  

How this is submitted: no submission necessary – we’ll look in Oracle

What is submitted: all tables corresponding to all entities in the ERD

3) Every table you create must have at least 10 rows per person  (eg. 20 rows for 2 person team projects, 30 for 3) rows of data with these exceptions:

a. CODES and ACT_FLAGS can have 3 per person (eg. 6 for team project) rows,

b. OWNER_HISTORY must have 15 per person (eg. 30 for 2 person team project) so that at least some of the people end up with an owner history of more than one person.

Data can be completely fictitious (but must be reasonable). Have fun with your data! You will be graded on spelling of data when appropriate (for example, if you spell name of states wrong, such as Pennsylvania, or other common English words, points will be taken off; if data is offensive and/or illegal, points will be taken off). You cannot type in nonsense. For example, entering a person name as “aldkjf;kljslkdfj” will be marked as a reduction in points. But you can make up data. It is likely that tables you use in queries will have many more rows that 5, but each table must have AT LEAST the given minimum. 

3) Other tables not required in the ERD MAY be created by you if you want to use them for queries. If you do create them, they must have at least 5 rows of data (except for validation tables with less than 5 options in a drop-down). All optional tables must follow the requirements of this assignment and the ERD assignment. If you create other tables, you must tell me so in a note at the beginning of the word document.

4) Create  2 business questions/problems/scenarios (Teams will create 2 per person – therefore 4 or 6) that could help you better understand the data in your database. The business problems/opportunities you are writing about may be from a marketing perspective (e.g. a realtor wanting to market to certain segments of the population), a planning perspective (deciding whether to allow certain types of businesses in certain municipalities), a demographics perspective (which people live where and how much do they pay for a property? Are zip code and sale price related? Is the ratio of sale price to assessed value different based on municipality?)  or any other type of problem for which the data can be mined. Come up with a question to be answered and write the SQL query to answer that question, then run the query and provide the output. Save your queries on the Oracle server in your account with the names Lastname_final1, Lastname_final_2, Lastname_final3, etc. Analyze the query output to answer your business question – I’m looking here for some value that the questioner would obtain from the query output. Although you can get ideas from the homework problems, your wording for this portion of the assignment should not read like you are assigning a homework problem to a student, but like you are stating a business problem or hypothesis. The two or four business problems should be submitted in a Word document with the following format:

Business Problem/Question 1: (paragraph in full sentences)

SQL for Question 1: (cut and paste from Oracle)

Output for Question 1: (cut and paste from Oracle into Word table)

Analysis of Output: (paragraph in full sentences)

This part of the assignment will be graded on the sophistication of the question and SQL.

How this is submitted: Document Via CourseSite Link (Week 15) 

Scripts should also be saved in the scripts area of your workspace in Oracle Express and labeled as Lastname_final_1, Lastname_final_2, Lastname_final_3, Lastname_final_etc

What is submitted: Word document – please name it with your last name (example: nonnemaker.docx)

NOTES on OUTPUT:

· You can do a screen print by using the print screen key. This will copy what is on the screen to the clipboard. You can then paste it into Word.

· You can then click on the image, crop it, and enlarge it as you see fit.

· Make sure you are showing ALL the rows in the output cut and pasted into the Word doc, not just the first 10.

· If the output is too many rows to show, make a note in the Word doc as to how many rows it is (say the output is 813 rows and you are only showing me the first 100 or something like that).

EXTRA CREDIT: You may submit one additional query (two for teams – each will count half) for extra credit consideration in your word document. You must clearly label it Extra Credit in the word document and call the query Extra in your Oracle space. If you do not label it, I will only grade the first queries for the project and ignore any further queries. The extra credit query(s) should be exceptional SQL. The total award of extra credit will be no more than 20 points on the total of the final project submission (out of 100%)  and would be for especially exceptional queries. For an extra credit query to receive ANY credit at all, the entire business problem, SQL, and analysis MUST BE EXCEPTIONAL. The business problem has to be a really outstanding, unique or insightful business problem or opportunity. The SQL must be exceptionally sophisticated with advanced concepts (perhaps something we did not even cover in class). The output must show exceptional care in your data population. The analysis of the output must be correct and show that you understood the problem and the output, and can clearly, carefully, and concisely apply the analysis to understanding the problem/question/scenario you proposed. 

Assignment Submission

You must upload the items noted above by the deadline in CourseSite. Your tables, the data and your Queries must be created in Oracle by the deadline.

Grading Criteria:

25% Tables created correctly in Oracle (Includes primary key, correct data types, foreign keys)

15% Data Population in Oracle

· Data can be manufactured (fictional) but must be reasonable; example: name can be Joseph, Catherine, Timothy, Jamal, Hashim, Varun, Purdy, Raja, Vijay  but may not be asdkldjl

· Numeric quantities, prices, etc. must be reasonable/believable; for example, if your table holds tax amounts and you have entered  $9,500,000.00 – this is not reasonable

· If you have a field that is a data type of CHAR(x), your data must use all x bytes; for example, your field is CHAR(10), all data for this field should be 10 bytes

· You have the proper number of rows in each table.

· You have correct spelling in the data (for example, you spelled Pennsylvania correctly)

60% (30%  or 15% for team efforts) Per EACH Query required, graded on:

· Spelling, grammar, and use of full sentences in business problem statement and analysis

· Whether the business problem makes sense and level of sophistication of its statement

· Level of complexity of SQL. I am looking for at least 5 each from among the concepts we have learned (e.g. joins, subqueries, correlated subqueries, single row functions such as date and to_char, fancy formatting, group functions, having, unions, concatenation, etc.)

· Formatting of output in SQL (rounding, heading of columns concatenating fields, formatting of output, proper formatting of dates, etc.)

· Whether the query output truly answers the question posed in the problem statement

· Depth of analysis of query output

· Ability of query to work with all data possible in the database, not just the data you entered. Think about other possible values that could be in the database, and how the output would look if the data were different. It is OK to have the analysis you present, but the query MUST execute with all possibilities of data values.

· Ability of the query output to make sense in the real world. It is OK for your query to output 5 rows, but what if the query ran against a real world database – would it output millions of rows and thus be impossible to be useful?

· If queries are just basically copies of homework solutions, they will earn at most 20 out of 30%, likely lower.

· Scripts will be run through a comparison tool.

Example of a Query that would receive a poor grade: 

Business Problem/Question 1: (paragraph in full sentences)

Netflix wants to know where most of their customers live.

SQL for Question 1: (cut and paste from Oracle)

select state, count(state) from addresses

group by state

Output for Question 1: (cut and paste from Oracle into Word table)

 

Analysis of Output: (paragraph in full sentences)

Most of Netflix’s customers are in IN and NC.

Why would the above query receive a poor grade?

· There is no real stated business problem or issue. Why does Netflix need to know where most of their customers live? Do they want to do an advertising campaign? Are they concerned about picking a new courier to deliver in this area of the country? What exactly is the problem?

· The SQL is straightforward and simple, and does not even answer the question stated. It counts the customers, but doesn’t answer where MOST of them live. You would have to do the PERCENTAGE of customers from each state to see if Indiana and North Carolina have the most rather than raw numbers that have to be compared and analyzed further. Further, the SQL ignores the fact that one customer can have 2 addresses, so it is not correctly counting customers either. The data should be sorted for easier analysis and the column headings fixed.

· The analysis doesn’t match the output. There are 140 customers (and 150 addresses). 49 of the addresses are in IN and NC, but that technically isn’t “most”. Those are the two states that have the highest number of customers coming from them, but it is not true that “most” or a “majority” of Netflix customers come from those states.

· The business problem and analysis are only one sentence – not enough to really explain what is going on or analyze the situation.

Common ways students miss points:

1. Making claims in the analysis that don’t fit the output from the query.

2. Hard coding items in the SQL, when instead a subquery  or other SQL could have been done to take the place of the hard coded item.  (It is OK to hard code items if the business problem calls for them explicitly, but only if this is the case.)

3. Asking in the business problem about a concept or database item, but not using it in the query.

4. Asking a really interesting business problem, but then just basically dumping the database in a query (not trying to do a JOIN or use a WHERE).

5. Not thinking about how a query would work in the real world on the real database with perhaps millions of rows. It needs to work for your small database, but should also be capable of working if the tables had many many rows and different data too.

6. Making judgments about data that is not modeled in the ERD you used.

7. Submitting a query that does not work.

8. Not including items in the query output that are needed to really understand the analysis. For example, if your business problem is to show that students who eat broccoli have higher GPAs that students who do not, you really should include in your output the average of the students who eat broccoli (3.9), those who do not (2.2), AND the  difference between two averages (1.7). It is much easier to understand and comprehend rather than just including the difference between the averages (just including the 1.7).

9. While it is appropriate to invent/”make up” information for business problems (something like “business x wants to have a joint venture with business y”, this must be reasonable. You must convincingly argue your case if you propose something like the linkedIn giving away free courses, for example, or company x hiring away all people who work in company y. Otherwise you may lose points.

10. Copying the theory of an SQL from a homework problem and using it for the final project. It is OK to get ideas from the homework assignments, but if you are just plugging in new database fields and tables to a homework from Netflix or Yelp, this won’t get you an A on that query.