共计 2261 个字符，预计需要花费 6 分钟才能阅读完成。
INFO151 2020-T3
Assignment 4 (30%)
1.An example of a lost item notice from a library to a client is depicted below. You need to organise a database to record this type of information. The lost item notice is issued if the item is still on-loan after 1 year.
Additional information: An item Barcode can determine the item location. There is only one type of related fees, the lost item replacement fee. A client can have multiple lost items in one notice. A client has a unique student ID number which is not shown in the example. In this exercise, there is no requirement to track the current status of any items in the table. There is no need to create a separate ClientID and Client_Name table.
(a)Write the Sqlite command line script to generate the HTML notice like the one shown above including the greeting and the wordings. Submit the script and a screenshot of the resulting notice. [8 marks]
2.An online student record is given below. You are required to develop a database to store the information for generating the record.
Additional Information:
- A status field records whether a student has completed or not completed the degree.
(a)Total credits points gained, and GPA are computed using aggregate SQL with the weight table without any hardcoding.
(b)The printed date and time are current system time.
(c)For computing GPA, the weights table (should be included in the database) is given below:
Fail 0
Pass 1
Credit 2
Distinction 3
High Distinction 4
The formula for calculating GPA is to divide the total points earned in a program by the total number of credits attempted. For example Zane Walker GPA = (26+36+46+36)/(6+6+6+6) =3.00.
(a)Construct the 1NF dependency diagram, name all the dependencies and indicate the primary key. [12 marks]
(b)Normalise the table into 3rd normal form. You must show the progress from 1NF to 2NF, and then to 3NF. Indicate all the primary keys and foreign keys in all the normalised tables. [8 marks]
(c)Write the DDL to create the normalised tables and indicate the sequence of creating the tables. Provide a screenshot for each table you created. Use appropriate entity names for example Student, Grade, etc. to represent the normalised tables respectively. Use only the affinity data type for the attributes in the tables. [8 marks]
(d)Write the DML in a correct sequence to insert the data of Zane Walker into the respective tables. Provide a screenshot of each table after the insert. Use only the single insert method for each table. [8 marks]
(e)Use the Sqlite command line method to generate a HTML file that will produce the online student record for Zane walker as shown in the diagram. Submit the script and a screenshot of the HTML page. [12 marks]
WX：codehelp
