Problem 6.2. Predicting Software Reselling Profits. Tayko Software is a software catalogue firm that sells games and educational software. It started out as a software manufacturer and then added third-party titles to its offerings. It recently revised its collection of items in a new catalogue, which it mailed out to its customers. This mailing yielded 2000 purchases. Based on these data, Tayko wants to devise a model for predicting the spending amount that a purchasing customer will yield. The file Tayko.csv contains information on 2000 purchases. Table 6.10 describes the variables to be used in the problem (the Excel file contains additional variables).
You are to write a brief discussion of your solution, i.e. how you approached the
modelling problem and any issues you may have encountered (maximum of ½ page)
2) You are to write all applicable business rules necessary to establish entities,
relationships, optionality’s, connectivity, cardinalities and constraints. If a many–to many relationships is involved, state the business rules regarding the bridging entities
after breaking down the many-to-many relationship. An example business rules format
can be found in Appendix A of this document.
3) Based on these business rules*, draw a fully labelled and implementable Entity Relationship Diagram (ERD). Include all entities, relationships, optionality’s,
connectivity, cardinalities and constraints. You must use Crow’s foot notation and
MySQL Workbench (or other software) to create the ERD. A Hand-drawn ERD will
NOT be accepted. A sample ERD can be found in Appendix A of this document.
(Note: The ERD created using the drawing tool (e.g. MySQL Workbench) will need to be
saved (or exported) as an image file and then be included in your document file to be
submitted)
4) A summary to describe the major justifications, assumptions and limitations related to
your database design. For example:
• Assumption/justifications for optionality, connectivity, constraints data type and
data domain; and
• Special cases or data integrity issues that cannot be handled.
Create Database named as university schema and then write the following queries in SQL, using the university schema.
a. Find the titles of courses in the Comp. Sci. department that have 3 credits.
b. Find the IDs of all students who were taught by an instructor named Einstein; make sure there are no duplicates in the result.
c. Find the highest salary of any instructor.
d. Find all instructors earning the highest salary (there may be more than one with the same salary).
e. Find the enrolment of each section that was offered in Spring 2020.
f. Find the maximum enrolment, across all sections, in Spring 2020.
g. Find the sections that had the maximum enrolment in Spring 2020.
h. Write down the query for deleting any table from the above created database
Using your knowledge of BITS, determine the functional dependencies that exist in the following table. After determining the functional dependencies, convert this table to an equivalent collection of tables that are in third normal form:
Assignment #2 – Parts Form
After successfully completing this assignment, you should be able to:
· Develop a process typically used in master file maintenance
· Control enabling and disabling functions available to the user
· Use a List Box for navigation
· Validate data provided by the user
This is an individual assignment, on a bound form, using the Scuba.accdb database. All code must be converted from macro to VBA.
When the form loads, the user should be able to browse through the records on file, using a List Box and navigation buttons, without being able to accidentally changing any data.
If the user wishes to change the current record, they would click the Edit button, which would change the form into modify mode, disabling navigation and all buttons except Save & Cancel, and opening all data fields, except the key, for updating.
If the user wishes to add a new record, they would click the Add button, which creates a new empty record, clearing the form, and changes the form into modify mode as described above.
When the user is finished and wishes to save the modified/added record, they would click the Save button, If the record passes validation, it’ll save the record to the database and shift the form back to browse mode: locking the inputs, enabling navigation and all buttons except Save and Cancel.
If instead the user wishes to abandon their edits or the new record, they would click the Cancel button, which would shift the form to browse mode as described above, and display the original record’s data before Edit or Add was clicked.
The Control Box, Navigation Bar and Selector Bar are hidden, and tabbing off the last field should stay on the current record so the user cannot break out of browse or modify mode in an uncontrolled manner.
Validation
When the user clicks Save, the current record is validated by a separate Validate method. If the record passes, the Save method saves the new/updated record to file. If the record does not pass the edits, all error messages are displayed at once to the user, one line per message, in either a form label or in a message-box pop-up … the data is retained on the form for the user to correct it.
· A vendor must be selected.
o The Selected Index on a drop-down (aka “combo box”) is -1 if nothing is selected.
· Description must be at least 4 characters long, after being trimmed of leading & trailing spaces.
· Cost must be greater than zero
· List Price cannot be less than cost
· Both cost and list Price should display as currency, two decimal places.
Hand In
Upload your database containing the form to the correct assignment folder on eConestoga.
Assignment #2 – Parts Form
After successfully completing this assignment, you should be able to:
· Develop a process typically used in master file maintenance
· Control enabling and disabling functions available to the user
· Use a ListBox for navigation
· Validate data provided by the user
This is an individualassignment, on a bound form, using the Scuba.accdb database. All code must be converted from macro to VBA.
When the form loads, the user should be able to browse through the records on file, using a ListBox and navigation buttons, without being able to accidentally changing any data.
If the user wishes to change the current record, they would click the Edit button, which would change the form into modify mode, disabling navigation and all buttons except Save & Cancel, and opening all data fields, except the key, for updating.
If the user wishes to add a new record, they would click the Addbutton, which creates a new empty record, clearing the form, and changes the form into modify mode as described above.
When the user is finished and wishes to save the modified/added record, they would click the Savebutton, If the record passes validation, it’ll save the record to the database and shift the form back to browse mode: locking the inputs, enabling navigation and all buttons except Save and Cancel.
If instead the user wishes to abandon their edits or the new record, they would click the Cancel button, which would shift the form to browse mode as described above, and display the original record’s data before Edit or Add was clicked.
The Control Box, Navigation Bar and Selector Bar are hidden, and tabbing off the last field should stay on the current record so the user cannot break out of browseor modify mode in an uncontrolled manner.
Validation
When the user clicks Save, the current record is validated by a separate Validate method. If the record passes, the Save method saves the new/updated record to file. If the record does not pass the edits, all error messages are displayed at once to the user, one line per message, in either a form label or in a message-box pop-up … the data is retained on the form for the user to correct it.
· A vendor must be selected.
o The SelectedIndex on a drop-down (aka “combo box”) is -1 if nothing is selected.
· Description must be at least 4 characters long, after being trimmed of leading & trailing spaces.
· Cost must be greater than zero
· ListPrice cannot be less than cost
· Both cost and listPrice should display as currency, two decimal places.
Hand In
Upload your database containing the form to the correct assignment folder on eConestoga.
a) DarwinCom Pty Ltd is made up of a number of departments that manage none or more projects.
Each project is made up of none or more team members. Each team member belongs to one
department and zero to one project. One of the team members supervise the other team members
on the project.
b) A company has four departments. Each department has one manager. Each department employs
staff. Each staff may work for one or more departments. A staff may be supervised by another
staff at least.
c) A car insurance company whose customers own one or more cars each. Each car has associated
with it zero to many number of recorded accidents.
d) A university registrar has the following entities: Courses (including course number, title, credits,
syllabus, and prerequisites); Course offerings, (including course number, year, teaching period,
instructors, timings and classroom); Students (including student-id, name, and program); and
Instructors (including identification number, name, department, and title). The enrolment of
students in courses and grades awarded to students in each course they are enrolled for must be
appropriately modelled. An instructor could teach in only one course. Each course only runs in
one session