**ASSIGNMENT 1 (50%)**
1) Using the set of data items provided for the case study, produce a set of relations in third normal form (20%).
2) Give a complete retrievals catalogue for the case study with, for each retrieval function, a definition specifying the business purpose of the function, the data accessed and the outputs produced (20%).
3) Use appropriate SQL features to implement a subset of the retrievals catalogue, concentrating on a few complex functions rather than many simple and repetitive retrievals. The retrievals implemented should also be of obvious business relevance to the organisation in the case study (60%).
It will be necessary to create appropriate database tables and populate them with sufficient data for adequate testing. However it is not necessary to create the entire set of tables.
For each function implemented give:
- a definition of the database tables used (both format and contents)
- a listing of the SQL procedure
- an example of the output from the procedure
1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done.
2) Installation package that will install the software (in ready-to-run condition) on the platform(s) specified in this bid request.
3) Exclusive and complete copyrights to all work purchased. (No GPL, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site).
**This assignment may be done using any software which supports relational database table creation and SQL, providing that the SQL is written by you rather than generated by the software and all the items in 7. below are submitted.**
**It should be submitted entirely on paper. Assignments or parts of assignments submitted on disc will not be marked. **
[url removed, login to view] the raw data and present in a set of tables in third normal form defined in the standard notation i.e
STUDENT (_STUDENT_CODE_, NAME, .......)
It may be necessary to add new data. If so, each new data item should be defined.
[url removed, login to view] a full retrievals catalogue for the case study and define each retrieval.
3. Choose the retrievals you wish to implement and make sure that you know how to write the relevant SQL. There will be more marks for a few complex procedures than for many simple procedures ??" but the complex procedures must be useful and must work.
4. Create the tables needed for the retrievals you wish to implement, making sure that they contain all the necessary fields and that the fields are sufficiently large. It is not usually necessary to create all the tables.
5. Make up data suitable for testing your retrievals and insert into the tables. This may be done by the SQL command, INSERT, or by Oracle Developer( which is quicker). Whichever method is used, check the response messages carefully to make sure that the data has been successfully inserted.
windows 2000,nt. and also on linux so is able to run on oracle- sql*plus 8