有人会Access数据库吗,或者懂SQL语言的
能不能帮点忙E-R modle Requirements
You are required to implement a relational version of the Searchlight database system, using the Microsoft Access database provided:
1. Download the provided Microsoft Access database and become familiar with its structure, operations and relational tables;
2. The query SetTapePrices has been used to default all tape prices to the same default value. Write a query IncreaseDVDPrices which will increase the price of all DVDs made since 2000 by $2. (Be careful not to run this query more than once!)
3. The query GenreCounts displays a count of the number of movies in each genre. Write a similar query RatingCounts to do the same for that field..
4. Create a query Shanghai to list the title and year made of any film with “Shanghai” in its title.
5. The query CastLists lists all films which have cast members recorded in the database. The query Catalogue lists all films where copies of videotapes and/or DVDs are available for sale. Manually compare these two lists, and identify a film whose cast list has not been stored in the database. Search the World Wide Web to find the names of cast members in your chosen film, and add the cast list into the database (anyway you wish). Demonstrate that this has been achieved by producing output from CastLists before and after the additions.
6. Some film titles appear more than once in the Film table, typically where a film like, say, “The 39 Steps”, is remade. Create a query Remakes that lists any film title appearing more than once.
7. Write a Query Stallone to list the title and year made of any film either starring Sylvester Stallone, or directed by him.
8. The query AnnualProduction lists the number of films made each year. Write a query MostProlificYear to identify the year in which more films were made than any other year. {Note: This is quite hard. An elementary solution could sort the listed data in descending order, but to produce only a one row output, you may need to explore the ALL operator}
9. Produce a form Customer and use it to add a new customer to the database.
10. Produce a form CastMembers that could be used to display the cast members of a film, having input the FilmID as a parameter.
11. Produce a report Sales listing for every movie purchased the number of videotape and the number of DVD copies sold. Produce a grand total for each of these.
12. A “filmography” for a person is a career list of films made by that person. The query Filmography and the report of the same name produce a consolidated filmography for the entire database. Use the query Catalogue to produce a report of the same name (Catalogue) that lists in title order, by genre, the rating and year made of all films where at least one DVD or videotape is held in stock.
13. The tables for implementing the shopping cart have not been implemented. Using the Purchase and PurchaseItems table as a guide, create tables that could implement the shopping cart feature. (Note: You do not have to put data in these tables, or use them in queries. You just have to provide narrative descriptions of how they would be used, now that you have created them, in the following two requirements)
14. Demonstrate how you would perform the following transactions:
• Delete any shopping cart, including its contents, created before today.
Note that both this requirement and the following one require several operations. You need to provide the details of the queries and modifications to the database contents required to perform these transactions in either SQL or the Microsoft Access 2000 (or 2003) query and transaction formats. Where a transaction involves multiple operations, you should explain, in English, the sequence of the operations, and the decisions to be made between each of these operations. You do not need to write or test any of your queries in Access.
15. Demonstrate how you would perform the following transaction:
• Transfer the items from a Shopping Cart, and turn them into an actual Purchase
16. Write a report of approximately 1,500 words that describes how you have satisfied each of the above requirements in your database system. This report should be in short report format. The entity Movie has been implemented as the table Film.
The table PurchaseItems is an implementation of the “includes” relationship between Purchase and Movie.
The table Cast implements the “was_cast_with” relationship between Movie and Person.
The Person table contains both movie directors and film stars.
The attributes Media, Genre and Rating have all been implemented using the Lookup feature of Microsoft Access. 做了一半的。 .谁帮我改改 看8懂....哎~偶老板让偶弄一个sql和access和excel互相转换还能用.net修改的东西....
fxxk~老子又不是搞编程的.... ....哎痛苦啊
以前学过,现在忘了!
唉......,看来帮不了忙了,精神上支持你! E-R模型图～～·莫非LZ也是软件工程？灭哈哈，我正在学 原帖由 =|HERO|=Yuchuan99 于 2007-6-5 14:16 发表 http://telecom.heroclan.net/images/common/back.gif
看8懂....哎~偶老板让偶弄一个sql和access和excel互相转换还能用.net修改的东西....
fxxk~老子又不是搞编程的....
全世界没几个会的 这哪叫ER图....
连表结构都没...
页:
[1]