Database代写：CS1032DatabaseDesign

代写数据库作业，需要完成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:
   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.

1. 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.
2. 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.
3. 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.