This was an university project where I designed and implemented a relational database system.
- A case study to analyse.
- An established connection to a server to store the created database.
- A set of requierements to implement:
- Identify Entities & Develop Business Rules.
- Construct a logical Entity Relationship Diagram (ERD).
- Use Normalisation to validate your ERD and derive the relational schema.
- Create tables.
- Populate data.
- Construct SQL Queries.
- Visual Paradigm CE 16.2
- Oracle SQL Developer
- Windows 10
- Microsoft Word
- A large white board, pen & paper!
The project consisted on five parts:
First part: Identifying the entities, and developing the business rules according to what was required from the case study.
The following image as a small example of the case study:
Syntax used for the bussiness rules: Each/A/An ENTITY_1 May/Must Relationship_Verb_Phrase number ENTITY_2
Any assumptios were stated to make sure that the requirements were meet respectively.
Overall, these were the identified entities:
Second part: Based on the business rules, a logical ERD was constructed using Visual Paradigm. Here, the primary & foreign keys, the disjoint & overlapping relatioships, the cardinality & participation, and any assumpitions were included. All the M:N relationsips resolved by creating multiple briding entities, and Crow’s Foot notation used to show connectivity. More details can be found on the image below:
The attributes' datatype were ommited in the diagram.
Third part It was neccesary to demostrate that the diagram was indeed normalised. This was simply stated in words.
Fourth, Fifth & Sixth parts
-
Creation of tables in Oracle SQL Developer. It was required that the database was created using the university's server connection. Create tables script file
-
Populating the tables with data. This was simply simulating data for educational purposes. None of it directs to a real person in particular nor describes anyone's real details.
Data script file -
Creating queries to retrieve selective information. The purpose of the query is stated before each's lines of code.
Queries
Using new technologies comes with a level of challenge, specially if it is the first time -which was my case. Inserting the data on the right place was troublesome at times given that the correct position from each attribute needed to be kept in mind. Creating a file to drop the tables was very useful. Sometimes the tables were not created properly or re-adjustments needed to be done according to the ERD. The queries were challenging to some extend as the sintax has to be followed when using FROM, JOIN, ORDER BY commands.
Although the tools used were completly new to me, the language and its sintax too, I found this to be interesting and very useful to learn. I look forward to work in more projects using SQL & RDBMS in a near future.
For educational purposes only. If you are an student: Do not copy & paste - learn.