Some problems:
- DateAdded, DateRequested, DateGiven, DateJoined should be 'Date' data-type
- 'Title' of the 'Book' table should not be PK
- 'Summary' of the 'Book' table should be Text data-type
- 'Name' of the 'Member' table should not be PK
- The relationship between 'Book' and 'Member' should be refined 1 to many from Member to Book
- There should have a relationship between Member and Borrowed (1 to many)
- Phenomenon of data inconsistency: 'Member' field in Borowed and 'Name' field in Member
- Phenomenon of data redundancy: 'Book' table, 'Borrowed' table
- Lack of information about newsletter
- Lack of information about the monthly-payment
Note: You should find out more problems for this scenario
You should design your own ERD, together with your own explaination
My proposal is one of the solutions which maybe not the best one.
Question 1: List member who have added some books this month:
Solution 1:
SELECT * FROM Member
WHERE MemberID IN(
SELECT MemberID FROM Book
WHERE MONTH(DateAdded) = MONTH(GETDATE()) AND YEAR( DateAdded) = YEAR(GETDATE())
)
Member*(ПMemberID(Ϭ MONTH(DateAdded) = MONTH(GETDATE()) Λ YEAR(DateAdded) = YEAR(GETDATE()) (Book))
Solution 2:
SELECT Member.* FROM Member INNER JOIN Book ON [Link] = [Link]
WHERE MONTH(DateAdded) = MONTH(GETDATE()) AND YEAR( DateAdded) = YEAR(GETDATE())
ПMember.*(Ϭ MONTH(DateAdded) = MONTH(GETDATE()) Λ YEAR(DateAdded) = YEAR(GETDATE()) (Member*Book))
Question 2: List members who have paid for newsletter this month:
Solution 1:
SELECT * FROM Member
WHERE MemberID IN(
SELECT MemberID FROM Payment
WHERE MONTH(PayMonth) = MONTH(GETDATE()) AND YEAR( PayMonth) = YEAR(GETDATE())
)
Member*(ПMemberID(Ϭ MONTH(PayMonth) = MONTH(GETDATE()) Λ YEAR(PayMonth) = YEAR(GETDATE()) (Payment))
Solution 2:
SELECT Member.* FROM Member INNER JOIN Payment ON [Link] = [Link]
WHERE MONTH(PayMonth) = MONTH(GETDATE()) AND YEAR( PayMonth) = YEAR(GETDATE())
ПMember.*(Ϭ MONTH(PayMonth) = MONTH(GETDATE()) Λ YEAR(PayMonth) = YEAR(GETDATE()) (Member*Payment))
Question 3: List member who have added some books OR have paid for newsletter this month:
Solution 1:
SELECT Member.* FROM Member INNER JOIN Payment ON [Link] = [Link]
INNER JOIN Book ON [Link] = [Link]
WHERE (MONTH(PayMonth) = MONTH(GETDATE()) AND YEAR( PayMonth) = YEAR(GETDATE())) OR
(MONTH(DateAdded) = MONTH(GETDATE()) AND YEAR( DateAdded) = YEAR(GETDATE()))
ПMember.*(Ϭ(MONTH(PayMonth) = MONTH(GETDATE()) Λ YEAR(PayMonth) = YEAR(GETDATE())) ᴠ (MONTH(DateAdded) = MONTH(GETDATE()) AND YEAR( DateAdded) =
YEAR(GETDATE())) (Member*Payment*Book))
Solution 2:
SELECT * FROM Member
WHERE MemberID IN(
SELECT MemberID FROM Book
WHERE MONTH(DateAdded) = MONTH(GETDATE()) AND YEAR( DateAdded) = YEAR(GETDATE())
)
UNION
SELECT * FROM Member
WHERE MemberID IN(
SELECT MemberID FROM Payment
WHERE MONTH(PayMonth) = MONTH(GETDATE()) AND YEAR( PayMonth) = YEAR(GETDATE())
)
Member*(ПMemberID(Ϭ MONTH(DateAdded) = MONTH(GETDATE()) Λ YEAR(DateAdded) = YEAR(GETDATE()) (Book)) U Member*(ПMemberID(Ϭ
MONTH(PayMonth) = MONTH(GETDATE()) Λ YEAR(PayMonth) = YEAR(GETDATE()) (Payment))
Question 4: List members who have not added anybook this month:
Solution 1:
SELECT * FROM Member
WHERE MemberID NOT IN(
SELECT MemberID FROM Book
WHERE DATEPART(MONTH, DateAdded) = DATEPART(MONTH, GETDATE())
AND DATEPART(YEAR, DateAdded) = DATEPART(YEAR, GETDATE())
)
Member*(ПMemberID(Member) - ПMemberID(Ϭ DATEPART(MONTH, DateAdded) = DATEPART(MONTH, GETDATE()) Λ DATEPART(YEAR, DateAdded) =
DATEPART(YEAR, GETDATE())) (Book))
Solution 2:
SELECT * FROM Member
EXCEPT
SELECT Member.*
FROM Member INNER JOIN Book ON [Link] = [Link]
WHERE DATEPART(MONTH, DateAdded) = DATEPART(MONTH, GETDATE())
AND DATEPART(YEAR, DateAdded) = DATEPART(YEAR, GETDATE())
Member - ПMember.*(Member * (Ϭ DATEPART(MONTH, DateAdded) = DATEPART(MONTH, GETDATE()) Λ DATEPART(YEAR, DateAdded) = DATEPART(YEAR,
GETDATE())) (Book)))
--------------------------------------
Notice that you have 2 hours for your exam, so I think you must make use of
time to do well your exam paper.
I think you should explain a little more about your decision, such as why you
design so, why you choose your answer and so on.
If you give the answers only, duration of 30 minutes is enough for you to
complete the exam. So it is not good!
