This project requires the design and development of an information system. The first part
of the project consists of the design, which includes construction of an ER diagram,
mapping to the relational schema, and further normalization of the relational schema if
required. The necessary information for schema design is given in the Universe of
Discourse (UoD) described below.
In the second part of the project, the designed schema will be implemented in MS
ACCESS. The implementation will also include creating tables, queries and others for the
system.
Universe of Discourse
The WEB PAGE case study is based on a database for Web pages, specifically for
maintaining a Web site related to courses taught at a university. Each course has an
associated Web site for maintaining all of the course material, including class notes,
associated publications, sample documents, executable files, and database files. This
courseware is stored in a particular directory on the Web site, and access is
provided by means of a file transfer protocol used for transferring files between
computers. The graphics associated with a Web page are also stored in a directory,
and the database records the display of a graphic by a Web page. Obviously, Web
pages are linked to other Web pages by hypertext transfer protocol links. Each Web
page is associated with the main Web page for a course, which is called its base.
You may assume the following:
• The characteristics of a Web page include a unique identification number, a title,
its Uniform Resource Locator (URL), which gives the address of the Web page,
and the number of times that the Web page has been accessed, or hits. A course
Web page has an associated base page and a base Web page serves as the base of
all of the Web pages associated with the course.
• The characteristics of a graphic include a unique identification number, the name
of the graphic, the format of the image, such as ‘gif’, ’jpg’ or ‘bmp’, and the
directory in which the graphic image is located. A graphic can be displayed by
many Web pages, and a Web page can display many graphics.
• The characteristics of courseware include a unique identification number, a
description of the course material, the directory in which the material is located
and a category describing the type of the course material, such as “P†for
publication, “N†for notes, “D†for sample documents, “E†for executable files
and “M†for database files. A courseware file can be linked to by several Web
pages and a Web page can provide ftp links to several courseware files.
Please provide these:
ER diagram and documented steps of the ER-to-Relational mapping process and any other design information (Normalization-if needed) as a MS Word file. If you are making any assumptions about the Universe of Discourse, which are affecting your schema design, than these must be documented and included in your submission entitled “EXTRA Specification Assumptionsâ€Â.
system files (MS ACCESS database file
(.accdb).
A list of your SQL and the output of each compiled queries as a MS Word file.
This project requires the design and development of an information system. The first part
of the project consists of the design, which includes construction of an ER diagram,
mapping to the relational schema, and further normalization of the relational schema if
required. The necessary information for schema design is given in the Universe of
Discourse (UoD) described below.
In the second part of the project, the designed schema will be implemented in MS
ACCESS. The implementation will also include creating tables, queries and others for the
system.
1
Universe of Discourse
The WEB PAGE case study is based on a database for Web pages, specifically for
maintaining a Web site related to courses taught at a university. Each course has an
associated Web site for maintaining all of the course material, including class notes,
associated publications, sample documents, executable files, and database files. This
courseware is stored in a particular directory on the Web site, and access is provided
by means of a file transfer protocol used for transferring files between computers.
The graphics associated with a Web page are also stored in a directory, and the
database records the display of a graphic by a Web page. Obviously, Web pages are
linked to other Web pages by hypertext transfer protocol links. Each Web page is
associated with the main Web page for a course, which is called its base.
You may assume the following:
•
•
•
2
The characteristics of a Web page include a unique identification number, a title,
its Uniform Resource Locator (URL), which gives the address of the Web page,
and the number of times that the Web page has been accessed, or hits. A course
Web page has an associated base page and a base Web page serves as the base of
all of the Web pages associated with the course.
The characteristics of a graphic include a unique identification number, the name
of the graphic, the format of the image, such as ‘gif’, ’jpg’ or ‘bmp’, and the
directory in which the graphic image is located. A graphic can be displayed by
many Web pages, and a Web page can display many graphics.
The characteristics of courseware include a unique identification number, a
description of the course material, the directory in which the material is located
and a category describing the type of the course material, such as “P†for
publication, “N†for notes, “D†for sample documents, “E†for executable files
and “M†for database files. A courseware file can be linked to by several Web
pages and a Web page can provide ftp links to several courseware files.
Schema Design (PART I)
Quality and correctness of schema design is a significant part of this project. The
following describe what is expected in the design part of the assignment.
2.1 ER Diagram (25 points)
Create an ER diagram to represent the conceptual schema described by the Universe of
Discourse. For cardinality and participation dependencies you may use either standard
notation, or the alternate (min, max) notation.
State clearly any extra assumptions you make regarding your design approach (I prefer
not to add any?). Please note that you cannot make assumptions to simplify or
compromise the completeness of the Universe of Discourse. If there are any points that
need clarification, in the specification of the Universe of Discourse as given above, you
must first attempt to clarify them with your instructor. As far as possible, refrain from
making your own assumptions.
2.2 Mapping (10 points)
Map the ER diagram created in 2.1 to a relational schema. Document the mapping steps.
The final schema should be given in the form of a schema diagram as given in your
lecture notes.
2.3 Normalization (15 points)
All relations resulting from the ER-Relational mapping should be normalized (when
required) up to the third normal form (3NF). Document your reasons for normalization,
clearly stating the normal form of the original relation, and of the resulting relation.
3
Implementation (PART II)
The following describe the scope and requirements of the information system to be
developed in MS ACCESS:
3.1 Tables and Forms (10 points)
Create all necessary FORMS to be used in order to populate the database with enough
meaningful sample data to allow us to test the functionality offered by your information
system. Each table should contain at least 10 rows. Again, you can use FORMS or any
other methods to populate your tables.
3.2 Queries and Reports (35 points)
Create necessary SQL queries that contain the necessary scripts to show all
of the following queries in your output. If any of the queries does not return
any results, then you are required to add the necessary records to your
tables to show that the query works.
[Q1] Which pages doesn’t contain (ftp links to) the class notes?
[Q2] Which pages display graphics having the name ‘asulogo’?
[Q3] Which pages do display any graphics?
[Q4] Which pages use ‘gif’ graphics but not ‘jpg’ graphics?
[Q5] Which pages ONLY use ‘jpg’ graphics?
[Q6] Which pages contain only two (http) link to another Web page?
[Q7] Which pages have the most hits?
[Q8] Which pages contain more than one (ftp) links to all courseware in the publications
category?
[Q9] For each Web page and graphic type, give the number of graphics of that type
displayed on that page. Display your results in descending order on webID and, within that,
in ascending order on graphic type (gType).
[Q10] For each group of pages belonging to a base, give the average number of hits, the
minimum number of hits in that group, the maximum number of hits in that group, and the
sum of the hits for that group. Display your results in ascending order on average number of
hits.
3.3 Constraints (5 points)
The system should enforce basic constraints, such as:
− Referential integrity. Multiple referential integrity constraints can be extracted
from the specification.
− Domain. Attribute values are restricted to the allowed data types.
− Key and Entity integrity constraints.
− Semantic constraints as given in the Universe of Discourse.
:
1. ER diagram and documented steps of the ER-to-Relational mapping process and
any other design information (Normalization-if needed) as a MS Word file. If you
are making any assumptions about the Universe of Discourse, which are affecting
your schema design, than these must be documented and included in your
submission entitled “EXTRA Specification Assumptionsâ€Â.
2. system files (MS ACCESS database file (.accdb).
3. A list of your SQL and the output of each compiled queries as a MS Word file.
Purchase answer to see full
attachment
