M
mitmelbourne
Hi friends ...
i have to solve the following queries in ACCESS...can u please help m
solving these...i am not into IT background but i HAVE to do this fo
my assignment...Please For GODS SAKE,,,help!!!!(For more details yo
can please refer the following link http://tinyurl.com/29fd6)
=====
Relational Database Schema
Store
Column name Data type Comments
StoreNum Text(3) Primary key.
ManagerName Text(30)
StoreAddress
Column name Data type Comments
StoreNum Text(3) Primary key. Foreign key „³ Store
Type Text(1) Primary key. Expected examples ¡¥R¡¦ for residential
¡¥M¡¦ for main and ¡¥P¡¦ for postal
Street Text(20)
City Text(15)
State Text(15)
Postcode Text(5)
StoreSupport
Column name Data type Comments
SupportingStoreNum Text(3) Primary key „³ Foreign key Store.StoreNum
SupportedStoreNum Text(3) Primary key. Foreign key -Store.StoreNum
VideoCopy
Column name Data type Comments
Barcode Text(15) Primary key. Assumed field type (barcode).
TitleNum Long Integer Foreign key „³ VideoTitle. AutoNumber storage.
StoreNum Text(3) Foreign key „³ Store. Which store stocks this copy.
Available Yes/No ¡¥Y¡¦ or ¡¥N¡¦
Fee Currency Each store may change a different fee for the same vide
copy
VideoTitle
Column name Data type Comments
TitleNum Long Integer Primary key
Title Text(20) In your data examples use short titles
YearPublished Date/Time Just year of the field ¡¥DateReleased¡¦
Duration Integer In minutes; (e.g. 150 for 2.5 hrs)
CategoryNum Integer Foreign key „³ Category
NewRelease Yes/No ¡¥Y¡¦ or ¡¥N¡¦
Category
Column name Data type Comments
CategoryNum Integer Primary key, assumed pre-existing range
Name Text(15) Name of the category; e.g. Horror, Documentary, Comedy
Family, etc
Role
Column name Data type Comments
TitleNum Long Integer Foreign key „³ Video. For storing an AutoNumber.
ActorNum Long Integer Foreign key „³ Actor. Another AutoNumber-storin
field.
Actor
Column name Data type Comments
ActorNum Long Integer Primary key.
Name Text(30)
Customer
Column name Data type Comments
VideoCardNum Text(10) Primary key
EmployeeID Text(10) Can be null ¡V if it is, there should be a row i
CustomerDetails.
CustomerDetails
Column name Data type Comments
VideoCardNum Text(10) Primary key; foreign key „³ Customer.
FirstName Text(15)
LastName Text(15)
Street Text(20)
City Text(15)
State Text(15)
Postcode Text(5)
Telephone Text(15)
Rental
Column name Data type Comments
TransactionNum AutoNumber Primary key.
VideoCardNum Text(10) Foreign key „³ Customer.
StoreNum Text(3) Foreign key „³ Store. Which store this is leant from
Barcode Text(15) Foreign key „³ VideoCopy.
DateRented Date/Time
DateDue Date/Time
DateReturned Date/Time Null indicates the video is still out.
=====
1.A list of videos at each store. Sort by and display, store number an
barcode.
2.A list of all videos sorted by and displaying, category name an
video title.
3.A list of all artists and all the categories that they have mad
videos in. Display artist name and category name.
4.A list showing the card number and surname of the customer and du
dates for all videos they have rented that have not yet been returned.
5.A list of all video titles for videos that star Bruce Willis.
6.A list of all video titles for videos that NOT star Bruce Willis.
7.A count of the number of videos in each category. Show the categor
number and count but show the column caption for the count a
¡¥Total¡¦.
8.A report showing the grand total of fees charged for all video
rented between 01/01/2004 and 01/04/2004.
9.A list of customer card numbers, the date rented, and the number o
videos rented, for customers that have rented more than 2 videos on th
same day.
10.A list of videos that have no major actors/actresses recorded
Displaying the video number will be sufficient.
11.What was the total value of fees charged for rentals made on th
most recent day of rentals for any store? Show totals for each store.
Thanks in advance...friends!!
i have to solve the following queries in ACCESS...can u please help m
solving these...i am not into IT background but i HAVE to do this fo
my assignment...Please For GODS SAKE,,,help!!!!(For more details yo
can please refer the following link http://tinyurl.com/29fd6)
=====
Relational Database Schema
Store
Column name Data type Comments
StoreNum Text(3) Primary key.
ManagerName Text(30)
StoreAddress
Column name Data type Comments
StoreNum Text(3) Primary key. Foreign key „³ Store
Type Text(1) Primary key. Expected examples ¡¥R¡¦ for residential
¡¥M¡¦ for main and ¡¥P¡¦ for postal
Street Text(20)
City Text(15)
State Text(15)
Postcode Text(5)
StoreSupport
Column name Data type Comments
SupportingStoreNum Text(3) Primary key „³ Foreign key Store.StoreNum
SupportedStoreNum Text(3) Primary key. Foreign key -Store.StoreNum
VideoCopy
Column name Data type Comments
Barcode Text(15) Primary key. Assumed field type (barcode).
TitleNum Long Integer Foreign key „³ VideoTitle. AutoNumber storage.
StoreNum Text(3) Foreign key „³ Store. Which store stocks this copy.
Available Yes/No ¡¥Y¡¦ or ¡¥N¡¦
Fee Currency Each store may change a different fee for the same vide
copy
VideoTitle
Column name Data type Comments
TitleNum Long Integer Primary key
Title Text(20) In your data examples use short titles
YearPublished Date/Time Just year of the field ¡¥DateReleased¡¦
Duration Integer In minutes; (e.g. 150 for 2.5 hrs)
CategoryNum Integer Foreign key „³ Category
NewRelease Yes/No ¡¥Y¡¦ or ¡¥N¡¦
Category
Column name Data type Comments
CategoryNum Integer Primary key, assumed pre-existing range
Name Text(15) Name of the category; e.g. Horror, Documentary, Comedy
Family, etc
Role
Column name Data type Comments
TitleNum Long Integer Foreign key „³ Video. For storing an AutoNumber.
ActorNum Long Integer Foreign key „³ Actor. Another AutoNumber-storin
field.
Actor
Column name Data type Comments
ActorNum Long Integer Primary key.
Name Text(30)
Customer
Column name Data type Comments
VideoCardNum Text(10) Primary key
EmployeeID Text(10) Can be null ¡V if it is, there should be a row i
CustomerDetails.
CustomerDetails
Column name Data type Comments
VideoCardNum Text(10) Primary key; foreign key „³ Customer.
FirstName Text(15)
LastName Text(15)
Street Text(20)
City Text(15)
State Text(15)
Postcode Text(5)
Telephone Text(15)
Rental
Column name Data type Comments
TransactionNum AutoNumber Primary key.
VideoCardNum Text(10) Foreign key „³ Customer.
StoreNum Text(3) Foreign key „³ Store. Which store this is leant from
Barcode Text(15) Foreign key „³ VideoCopy.
DateRented Date/Time
DateDue Date/Time
DateReturned Date/Time Null indicates the video is still out.
=====
1.A list of videos at each store. Sort by and display, store number an
barcode.
2.A list of all videos sorted by and displaying, category name an
video title.
3.A list of all artists and all the categories that they have mad
videos in. Display artist name and category name.
4.A list showing the card number and surname of the customer and du
dates for all videos they have rented that have not yet been returned.
5.A list of all video titles for videos that star Bruce Willis.
6.A list of all video titles for videos that NOT star Bruce Willis.
7.A count of the number of videos in each category. Show the categor
number and count but show the column caption for the count a
¡¥Total¡¦.
8.A report showing the grand total of fees charged for all video
rented between 01/01/2004 and 01/04/2004.
9.A list of customer card numbers, the date rented, and the number o
videos rented, for customers that have rented more than 2 videos on th
same day.
10.A list of videos that have no major actors/actresses recorded
Displaying the video number will be sufficient.
11.What was the total value of fees charged for rentals made on th
most recent day of rentals for any store? Show totals for each store.
Thanks in advance...friends!!