During the course of the Fall semester, I was asked to create a simple business model, which I would eventually flesh out into a conceptual model and eventually a physical database. I chose to create a simple presentation-media model for a small Church. Before knowing too much about my model, I wrote down ten questions that I would ask of this database. In the end, I had to rewrite 90% of the questions, due my increased knowledge of relational databases. The initial questions are as follows:
Original Questions
- List all of the people responsible for section ____?
- How many songs does _____(SongBook) have?
- When is the last time someone updated _____(SlideShow)?
- What media did _____(User) upload?
- How many slides are in the slideshow?
- What are the differences between this weeks schedule and last weeks?
- What is the projector displaying at this moment?
- Is there one or multiple projectors on?
- Which songs need to be updated?
- What was the title of the sermon preached one month ago, and by whom?
From those ten questions, I created my first model.
 |
| Initial Model (MySQL Workbench Database Model) |
During the whole process, and after many changes and simplifications, I had my final model. Looking at my questions, I realized that some of them were naive, and others just too simple. This moved me to create a new, and more realistic set of questions.
 |
| Final Model (MySQL Workbench Database Model) |
Updated Questions (and answers)
1. Which individuals have a presentation that hasn’t been presented?
SELECT CONCAT(I.first_name," ", I.last_name) AS "Name", P.presentation_id
FROM dba13.individual AS I, dba13.presentation AS P, dba13.individual_has_presentation AS IP
WHERE I.login_name = IP.individual_id
AND IP.presentation_id = P.presentation_id
AND P.date_presented IS NULL
2. What is the title of last week’s sermon(s)?
SELECT C.content_title AS "Sermon Title", P.date_presented AS "Presented On"
FROM dba13.content AS C, dba13.presentation AS P, dba13.presentation_has_content AS PC, dba13.content_type as CT
WHERE P.date_presented = (current_date - 7)
AND P.presentation_id = PC.presentation_id
AND PC.content_id = C.content_id
AND C.content_type_id = CT.content_type_id
AND CT.content_type = "Sermon"
3. What was the title of the sermon preached one month ago from today (Sunday), and by whom?
SELECT CONCAT(I.first_name," ", I.last_name), C.sermon_title
FROM dba13.individual AS I, dba13.individual_has_presentation AS IP,
dba13.presentation AS P, dba13.presentation_has_content AS PC,
dba13.content AS C
WHERE I.login_name = IP.individual_id
AND IP.presentation_id = P.presentation_id
AND P.presentation_id = PC.presentation_id
AND PC.content_id = C.content_id
AND P.date_presented = (current_date - 28)
4. What content does _______ (presentation) have?
SELECT C.content_title
FROM dba13.presentation AS P, dba13.presentation_has_content AS PC,
dba13.content AS C
WHERE P.presentation_id = PC.presentation_id
AND PC.content_id = C.content_id
AND P.presentation_id = 2
5. What content is unused?
SELECT C.content_id, C.content_title
FROM dba13.content AS C
WHERE C.content_id NOT IN
(SELECT PC.content_id
FROM dba13.presentation_has_content AS PC)
6. Who assigned the role of _______ (individual)?
SELECT I.role_assigned_by
FROM dba13.individual AS I
WHERE I.login_name = 'lookahead'
7. What presentations are blank?
SELECT P.presentation_id
FROM dba13.presentation AS P
WHERE P.presentation_id NOT IN
(SELECT PC.presentation_id
FROM dba13.presentation_has_content AS PC)
8. Who else used the content that _______ (individual) created, in their presentation?
SELECT I.login_name, PC.content_id, X.content_id
FROM dba13.individual AS I, dba13.individual_has_presentation AS IP, dba13.presentation_has_content AS PC, (SELECT *
FROM dba13.individual AS CO, dba13.individual_has_content AS IC
WHERE CO.login_name = 'john_r1'
AND CO.login_name = IC.individual_id) AS X
WHERE I.login_name = IP.individual_id
AND IP.presentation_id = PC.presentation_id
AND PC.content_id = X.content_id
AND I.login_name != X.login_name
No comments:
Post a Comment