代写数据库作业，需要完成E-R Diagram的绘制，然后根据设计，完成数据库的建表和查询语句的编写。
Project 1: Entity Relationship Diagram
Using an E-R drawing tool, create the following E-R Diagram. (highly recommended: Dia.exe - see OWL : Course Resources for details)
You can NOT hand draw the diagram - hand drawn diagrams will NOT be accepted or graded.
This project requires you to create a database design. Your design will be documented in a set of
Entity-Relationship diagrams using the representation as shown in the lecture materials. Draw a set of Entity-Relationship diagrams to model the following scenario.
You realize that in order to run your company you will need to start tracking some basic information that deals with your customers, your products and invoices.
You need to track the following:
For the customer, your design must be able to store:
- the customer’s Last Name and First Name.
- the customer’s complete address (street, city, prov, postal code)
- the customer’s phone number
- the customer’s status (first time buyer -or- returning customer)
For the product, your design must be able to store:
- the product’s name (i.e. Smiley Pin, Comic Book Bow Tie, Niblick Soft, etc.)
- the product ‘s classification (i.e. shirt, novelty, edible, pillow, pant, shoes, etc.)
- the product’s retail cost to the consumer (price in dollars).
- the product’s size (Small, Medium or Large)
- the product’s shipping weight classification (Light, Medium or Heavy)
For the Invoice (when a customer orders a product), your design must be able to store:
- the invoice date
- the invoice total amount
- the invoice status (New, Shipped or Paid)
HINT: a field is missing in each of the above. Hopefully, it will be very obvious.
Next, you need to store the following relationships:
When a customer orders a product, an invoice will be created. A single customer is associated to a single invoice. An invoice can only be made out to a single customer.
A customer must have at least one invoice associated (otherwise why would they be in the system?). A customer can have more than one invoice if they have order a number of times from your company. An invoice must have a customer associated (otherwise, why was it created?). An invoice can only be associated with a single customer.
An invoice will contain at least one product. It can contain more than one product if the customer orders multiple items.
An invoice must have at least one product associated with it. A product might not ever have been order, so it is might not be associated with any invoice.
The customer can return the product after having received it. These products were purchased by the customer. A customer can return many products and a product can be returned by many customers.
A customer might not have any returns listed (never returned a product) and a product may never have been returned by any customer.
The date the customer returned a product will also be saved in the database. hint: This data point is associated with the return and not the customer or the product directly.
You will create your answer to this project using a graphic design tool.
It is highly recommended that you use Dia.exe (- see OWL : Course Resources for details). You can also use Microsoft Word, or any other tool AS LONG AS THE output is an image or a PDF file so the Teaching Assistants CAN OPEN AND VIEW THE DOCUMENT !!!) It is your responsibility to ensure the document can be read by them; otherwise they will NOT be able to grade this project.
The file must be named:
youraccountname_ER_diagrams.??? (where ??? is whatever format you saved the document in)
To create your ER Diagram you can do one of the following:
- Use Dia.exe to create your document, then:
Export it as an image: i.e. youraccountname_ER_diagram.jpg
Print it as a .pdf if your computer has a PDF printer option. - Use a graphics editor to create your diagram and save it in JPEG format with the name: youraccountname_ER_diagram.jpg
- Draw the diagram using MS Word and save it in youraccountname_ER_diagram.docx
Attach the file youraccountname_ER_diagram.??? to your submission.
You must identify yourself on the document. The TA will NOT grade the document if this is missing. Somewhere visible on the actual drawing or Word file you must include:
- your first and last name
- your Western ID (see below for a description of your Western ID)
- your student number
NOTE: the ER diagram MUST be sent as either an image (picture) or as a PDF file.
Dia.exe allows the export as a .jpeg
Other ER tools must be outputted as an image or a PDF file.
Do NOT send the native (.dia etc.) file. The TAs can NOT read these formats !
Project 2: Create a Microsoft Access database
Using Microsoft Access, create a database based on your E-R diagram from Project 1.
The database must have all the tables required from your design.
- hint, there should be five (5) tables in your database
PART 1
Create a new table called “CUSTOMER” with the following specifications
1) Unique Customer ID that automatically created when a new item is entered note: this ID must start with YOUR initials: so - if your name is Dolly Madision - every supply ID would start with DM example:
DM0001, where "DM" are YOUR initials
DM0002, where "DM" are YOUR initials
DM0004, where "DM" are YOUR initials
DM0007, where "DM" are YOUR initials hint: MUST be 2 initials and four digits (see examples above) use Autonumber (Long Integer)
NOTE: - very important: if the Customer ID is used as a Foreign Key in another table:
- that key must be of type Long Integer
- the value entered will just be the number:
example:
1 where the Customer ID is DM0001
2 where the Customer ID is DM0002
4 where the Customer ID is DM0004
7 where the Customer ID is DM0007
2) Customer’s Last Name - maximum 50 characters
3) Customer’s First Name - maximum 35 characters
4) Customer’s Street Address - maximum 30 characters
5) Customer’s City - maximum 30 characters
6) Customer’s Province - maximum 2 characters display in all capital letters (i.e. ON or AB etc.)
7) Customer’s Postal Code - maximum 7 characters format: A1B 2C3 (LNL NLN - L: letter N: number - must use an Input Mask)
8) Customer’s Phone Number - maximum 10 characters stored as: 5195552323 (no brackets or dashes)
9) Customer’s Status (Yes/No or True/False or 0/-1) indicates FIRST TIME BUYER or RETURNING CUSTOMER.
Create a new table called “PRODUCT” with the following specifications
1) Unique Product ID that is NOT automatically created when a new item is entered note: this ID must start with ANY alphabetic character followed by 4 digits: the letter MUST be a capital letter example:
K-9345
F-0302
X-3000
note: it will not be the same letter each time.
Format: L-NNNN - where N means a number and L means a capital letter
- dash is required
- use an input mask.
NOTE: - very important: if the Product ID is used as a Foreign Key in another table:
- that key must be of type Text (String)
- the value entered will just be the letter and the number (no dash):
example:
K3433 where the Product ID is K-3433
L0090 where the Product ID is L-0090
P0300 where the Product ID is P-0300
2) Product Name - maximum 150 characters
3) Product Classification - (i.e. shirt, novelty, edible, pillow, pant, shoes, etc.)
4) Product Price - stored as currency
5) Product Size - (Small, Medium or Large) or (Compact or Full Size) etc.
6) Product Weight - in kilograms - stored as a number with one decimal place only.
Create a new table called “INVOICE” with the following specifications
1) Unique INVOICE ID that is NOT automatically created when a new item is entered note: this ID is made up of numbers only, but can start with zero (0):
example:
15365254
00025
1000101
note: make this a reasonable maximum length.
2) Invoice Date:
Date (short format) when this invoice was created.
3) Invoice Total - NOT stored as currency
stored as a number with two (2) decimal places
4) Invoice Status - can be stored as characters or a code (number) - up to you. (New, Shipped or Paid)
PART 2
You must build all the relationships described in your diagram. You must use the Relationships Database Tool in MS Access to signify the relationships.
You must fill in the table with example data of at least three (3) records for each table.
Your name must be the name of the first customer.
You are allowed to make up the names of the other customers and the products. The invoices should be created to appear valid and should make sense in the context of your other data.
Complete the above as required saving the database in your “Business” database. i.e youraccountname_Business.accdb (or .mdb for earlier versions)
Project 3: Information Systems Questions about Your Company
Create an MS Word document and complete the following questions pertaining to the business you described in Assignment One (1).
Each answer must be comprehensive (more than one sentence). Each answer requires at least four sentences. The entire Project 4 should be at least approximately 600 words. It is expected that some thought and explanation is included in this section.
- What Costs (money out) can you identify based on a business run out of your basement with just yourself as the only employee?
- list the cost and the estimate of how much.
- What type of data do you think you will to track in the beginning?
- this should be at least a couple of paragraphs (minimum 300 words) in length.
- Describe why you think your product will succeed (why did you select this).
- this should be at least a couple of paragraphs (minimum 300 words) in length.
The format of this document should be identical to format you used in Assignment One (1).
Place your name, followed by the company name at the top.
Fill in the required information after.
Formatting is not important as long as the document is easy to follow:
This document must be a Word file saved and submitted as a .doc (or .docx) file
The name must be a combination of your Western Account Name and the name of your company.
The file name must be youraccountname_companyname_A2.doc (or .docx)
- example (from above) dernt373_MaggicSoftware_A2.docx
Submission Instructions
You must upload and submit, via the CS1032 Web Site, ALL three (3) of the following files:
youraccountname_ER_Diagram.pdf or youraccountname_ER_Diagram.jpg (or other image type)
youraccountname_Business.accdb (or .mdb for earlier versions)
youraccountname_yourcompanyname_A2.docx (or .doc for earlier versions)
It is your responsibility to ensure the files have been submitted in OWL. Please check and make sure you have received the confirming email and then check that the three (3) files (you must submit three (3) files for this assignment) have been uploaded correctly.
You must do all three (3) Projects in this assignment. This is Assignment Two, comprised of three (3) parts, Project 1, Project 2 and Project 3. All three projects are to be completed and submitted. There was confusion on Assignment One regarding what was required.