Website编程设计讲解、SQL编程语言调试、SQL语言程序辅导
讲解数据库SQL|讲解Database
Application Requirements
Crazy Lou’s Music Website
The company “Crazy Lou’s Music” is an online retailer of old music converted to electronic format. All
the music is only available by downloading from the company’s web site.
Albums are organized into the following categories (keep in mind an album may belong to more than
one category) Rock, Country, Jazz, and Pop.
In order to use this web site the user will need to have an account. Once the user has created an
account the user can select the category of music they wish to view. When the category is selected the
system will display all the albums that are assigned to this category. The customer will use their email
address and their password to log into the application.
Steps required to complete the final Project
1- Identify entities and then describe the entities
2- Normalize the entities
3- Create Application Interface - prototype
4- Create the Database and populate with data
5- Programming Requirements – your application must do the minimum of the following
a. Create / Modify User’s profile
b. Log a User into the application – ensuring to create the session to track the user
c. A user must be able to order an album and must also simulate paying for the album.
d. Must generate a receipt page.
You can use the following steps to help assist you with designing the application or you can use your
own design.
Helpful Hints
Exercise Database Basics (Step 1)
Throughout this course, you will be building a data driven web application (Crazy Lou’s Music)
based on the Application Requirements. One of the first steps in any application is to determine
the data the application will need to store. In this exercise, you are required to identify entities
and then describe the entities (create fields) based on the applications requirements. If you
have any questions about the application’s requirements, you are to treat your instructor as the
customer and send your questions to your instructor.
Since the Album is organized by category, and the user will be able to search by category, it
makes sense to store the categories in a table as well. If the company decides later, to add a
new category, all that would need to be done, will be to add a new record in the category table
and then assign Albums to the category. Keep in mind additional entities or tables may be
required.
Account information
2
To create a user account the following must be stored:
- Customer’s name
- Customer’s Billing Address (only one address can be stored. Any changes to the address
will be applied here. However you still need to create the address table)
- Customer’s phone number (the customer may have several phone numbers, Home,
Work, and Cell)
- Email address (the database should only allow the email address to be entered once
into the database)
- Password
The categories that will be used for this application are the following
- Rock
- Pop
- Country
- Hip Hop
The following information will need to be stored about the Albums
- Album Name
- Artist or Group ( only have the artist or group appear in the database once) This should
be numeric field which will represent the ArtistGroup ID
- Retail Price for the Album
- The category the Album belongs to
- The songs that appear on the album
Information about the Artists or group
- Must store the artist or group name (the group or artist should not be duplicated)
- ArtistGroupID
- Artist Name
A Sessions table will need to be created to keep track of the user as they move through the
application
Exercise Normalization (Step 2) (Define each required table)
In the previous exercise you identified the entities required for the Crazy Lou’s Website application. In
this exercise you will normalize the database. The following should be kept in mind when you normalize
the database:
- Each entity should be its own table
- Make sure each field is stored in its smallest form (Example Name is stored as First Name, Last
Name)
- Identify Utility Tables (Country, Provinces, Category)
- Identify tables required to reduce data duplication (Example an album will contain several
songs. These would be repeating fields if stored in the album table)
3
- Make sure each table has a primary key.
This exercise may take a little of time. It will require you to keep in mind the database must be able to
grow as the needs of the application changes. Once you have identified all the tables required and all
the fields in each of the table, send your normalized table design to your instructor. Once your
instructor has indicated you have successfully completed this section, then you can move onto the
Create the database section.
Interface consideration (Step 3)
When you are identifying the entities, keep in mind the interface of the application. It is understood,
whenever possible, a drop down list should be used for known items. An example of this would be
country (this site will only be used for Canada but later they may wish to open the site up to other
countries so create the country table). When the address is entered the user will also need to identify
the province portion of the address. Therefore it would make sense to store the province in a table.
In this part of the application’s design you will need at least the following pages.
- Index.php – the main page when the user first visits the website
o The user will be able to login on this page or be redirected to create a profile
- Create Profile page – when the profile is successfully completed redirect the user to login
- Once the user is logged in they will be redirected to their landing page, which will have the
following menu options
o Home – to return to their landing page
o Profile – this will allow the user to modify their profile (Profile page)
o Order – this will allow the user to order music.
A new order record must be created
A new order details record must be created
Must have a payment page .. simulate a credit card being entered
A payment record must be created
Receipt Page – just display what was ordered and the customer info
o Contact Us – this will contain your name
- Create the interface and then move from page to page. This will require some simple redirect
code to simulate the user going through the application.
Create the Database (Step 4)
In Step 1 you identified all the entities the Crazy Lou’s web site would need. In Step 2 you identified the
tables required for the application and you normalized the tables, to ensure there was no data
duplication and the database could grow if required. In this section you will use the PHPMyAdmin to
actually create the database. Create the database as CrazyLou + your last name. Example: your name is
John Smith, the database should be created as CrazyLouSmith.
Once the database is created you will then need to create all the tables within the database. Keep in
mind each table should have an auto increment field as an ID field. Each table should have a primary key
and each field will need to be of the appropriate data type and length. (The Int data type does not
require a length but the varchar datatype does require a data type.
4
When you have finished the project, you are to Export the database and tables and send the .sql file to
your instructor. Make sure you place a check mark in the Create Database under the export options.
Once your database has been created you will need to enter data into each of the tables.
Enter into the Category Table
- Rock
- Pop
- Country
- Hip Hop
You will need to populate the following tables with some data
- Category
- Country
- Provinces
- Group (Enter some musical groups)
- Albums (enter some album names)
- Create the relationship between the Groups and the albums. (Remember a group may have
more than one album)
- Create the relationship between the Album and the Categories (Remember an album can belong
to more than one category)
- Enter a few customers information, keeping in mind you will have to manually create
corresponding records in the Address table and the Phone numbers table. (This can be done
from the interface, by using the Create Profile option in your application you are building)
Data Access Requirements (Step 5)
You learned in this course how to identify all the data access requirements for an application. In this
exercise, you will identify the data access for each web page in your application.
Keep in mind you will also need to identify the data access for the set up process for each web page. All
data access to be done using procedure. Make sure you name the procedures, so that, from the name of
the procedure your instructor can understand what the procedure will be doing. Follow the naming
convention shown throughout this course:
- When retrieving information from a table, the name of the table should be used along with the word
load in the name of the procedure.
- When updating a record, the name of the table being updated along with the word update. Your
document should follow the following format: Page Name Setup data requirements --- will identify all
the data access to set up the page for use by the customer Process --- will do the main processing of the
page
Connection to the Database (Step 6)
5
You have built the database, the interface and have determined all the data requirements for each web
page. The next step in the project is to connect to your database. In this section you should be adding
the following to your application.
Create a Profile Create – this is the section of the application to allow a user to create their profile. Keep
in mind, the Customer information should be saved in the Customer table. The Address should be saved
in the Addresses table and the phone numbers should be added to the Phone numbers table.
After you create a record in the customer table, you will need to retrieve the newly created customer ID.
This will be required, to be able to link the addresses record and the phone number records to the newly
created customer. You should remember the password is to be encrypted.
Login Process After the user has created a profile. You should provide a method to allow the user to log
into the application. Keep in mind when the customer record was created the password was encrypted,
meaning when you verify the user entered the correct credentials the password will need to be
encrypted again.
Load Profile. After the user logs into the application and the user clicks on the Profile menu option, all
the information related to the customer should be presented. You are required to actually update the
profile. (It would be a good idea to be able to update a user’s profile, but is not required)
Order Processing (Step 7)
In the previous exercises you created a user. In this exercise you will allow the user to place an order.
Make sure when the user creates an order the following is accomplished
- An order record is created
- An order details record is created
- A payment record is created
- An Invoice is created.
In the video for this section, it was demonstrated how to accomplish all these things. The user should be
permitted to place more than one item on the order. Keep in mind when the second item is added to
the order, the order details records should have the same order ID number.
When you have completed this exercise, please zip up the project and sent it to your instructor.