Entity modelling
1. In a student library, students are allowed to withdraw a maximum of four books for two weeks and are send reminders and incur fines of £0.50 per day on overdue books. Design a suitable entity model.
2. An estate agent keeps records of sellers and the properties they want to sell, people who want to buy and the characteristics of the properties they want, the sales that are going through and the progress of the sales, and the individual estate agents dealing with each sale. Design a suitable entity model.
Normalisation
1. Normalise the following file, stating any assumptions you make. The file is to contain details of telephone customers and the calls they make. Each customer can have one or more line numbers and many calls are made from each line. Each month one invoice is sent to each customer, showing their balance from the end of last month, any payment they have made this month, and a list of calls by calling line number.
Customer ( CustomerNumber, Name, Address, InvoiceNumber, InvoiceDate, CallingLineNumber, TimeOfCall, CalledLineNumber, Duration, Cost, BalanceAtEndOfLastMonth, PaymentTowardsLastMonthsBalance, TotalCallChargesForThisMonth)
Having normalised, draw the corresponding entity model.
Access database design
1. Create an Access database for the student library system described above.
2. Create an Access database for the estate agent system described above.
3. Create an Access database for the telephone billing system described above.
SQL
Using the accts.mdb database (schema shown in appendix A):
1. Write an SQL SELECT statement to list all customers who have at least one payment.
2. List all customers who have no payments.
3. List all customers who have invoices for which no payment exists.
4. Why, in the accts.mdb database, is the following query bound to return no records?: 'List all customers who have a payment for which no invoice exists.'
5. You have some embedded SQL that looks like this:
y$ = text1.text
x$ = "select * from customer where sname = " &
"'" & y$ & "'"
set rs1 = db1.openrecordset(x$, dbOpenSnapshot)
Can you foresee any problem with a customer whose surname is O'Brien? Try it.
Example due to J.M. Carter. 11-2-2000.
Access Forms, Macros and Reports
1. Create suitable Access input forms for the library system described above.
2. Create suitable Access input forms for the estate agent system described above.
3. Create suitable Access input forms for the telephone billing system described above.
4. Create suitable Access reports for the library system described above.
5. Create suitable Access reports for the estate agent system described above.
6. Create suitable Access reports for the telephone billing system described above.
Visual Basic Database Programming
Using either data controls, DAO, or ADO code:
1. Create suitable VB input forms for the library system described above.
2. Create suitable VB input forms for the estate agent system described above.
3. Create suitable VB input forms for the telephone billing system described above.
4. Create suitable VB reports for the library system described above.
5. Create suitable VB reports for the estate agent system described above.
6. Create suitable VB reports for the telephone billing system described above.