This assignment consists of design and development of an information system. The first part of the assignment consists of the design, which includes construction of ER and DFD diagrams, mapping ERD 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 assignment, the designed schema will be implemented in MS Access. The implementation will include creating forms, queries and reports for the system. The functionality of these forms and reports is also given below.
1 Universe of Discourse: Content Services for Mobile Phone Users
With the high speed broadband communication networks become widely available, mobile phone users can now use mobile phone to retrieve content from the Internet. Internet Content Providers (ICP) will need to understand different needs from a variety of users and package the content to cater for different content requests. On the other hand, Content Creators will also organize their content materials to be available to users through the ICP. This project is to create an information system for managing three types of users: (1) For ICPs to manage their online content services including the management of mobile phone users, associated Content Creators, and their requests of contents; (2) For Content Creators to deliver and update their content materials; (3) For mobile phones to subscribe the content services.
An ICP has a registered business name, an ABN (Australia Business Number), an Internet domain name, a list of its content services, and contact details (phones, address emails etc).
A Content Creator is identified by a registered business name, an ABN (Australia Business Number), a brief description of the content service, a list of categorised materials, and contact details (phones, address emails etc).
A mobile user is identified by the mobile phone number, the user details (including address, email, etc), and a mobile phone package plan number.
A mobile phone package plan can be described by the plan number, plan description, the price, minimum time period, etc.
An on-line deliverable content material is an electronic file that can be a text message, a video clip, a piece of music, or a picture. It is described by the creator?s name, content material name, its content category, production date, product description, expiry date, etc. Content materials can be delivered to mobile phone users with different unit price at different times, for different types of users.
A content category has a set of content materials. A category can have sub-categories. A category is described by its category ID, name, description, etc.
A content request can be initiated a mobile phone user who search for the availability of the content materials and book for its delivery on his/her mobile phone at certain times with certain costs.
A mobile phone will be charged for a cost whenever a content service request is satisfied by an ICP. So an ICP should be able to access the mobile phone user accounts and their account balance.
A user profile is a history of a set of the requested content materials and their categories that a mobile phone user has requested over a period time. By using user profiles, an ICP may be able to predict the interest trends of users.
A mobile phone user may subscribe services from different ICPs. An ICP may have many contracted Content Creators to supply content materials. An ICP will access the accounts of the Content Creators in order to pay for using their content materials. A Content Creator may supply their materials to many ICPs.
An ICP can log in the system to insert, delete, and update the ICP details and the content category details. A content creator can log in the system to insert, delete, and update the content creator details and the content materials. A mobile phone user can log in the system to insert, delete, and update the user details and his/her account details in an ICP.
For the content creators, whenever their content materials are provided to mobile phone users via an ICP, there will be a pre-determined amount of payment to be transferred from ICP to the content creator?s account.
The ownership of content materials belongs to the Content Creator. The services provided by an ICP are to organize the content materials and to satisfy the mobile phone users? requests. For example, a mobile phone user can subscribe the content services for weather, news, financial and stock market information, sports, music, or e-learning materials. The incentive an ICP can give to a Content Provider is to rank their content first to let mobile phone user to find it quicker and easier.
2 Database Schema and DFD Design
Quality and correctness of schema design is a significant part of this assessment (assessment below). The following describe what is expected in the design part of the assignment.
2.1 ER Diagram
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, but not both.
State clearly any assumptions you make regarding your design approach. Please note that you cannot make assumptions to simplify or compromise the completeness of the Universe
3
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 tutor and/or lecturer. As far as possible, refrain from making too many assumptions.
2.2 Mapping
Map the ER diagram created in 2.1 to a relational schema. Document the mapping steps. The final schema should be given in the notation as given in your lecture notes, e.g. R1 (keyattr, attr1, attr2, ...). In order to identify referential integrity constraints, be sure to either use the same names of the referenced/referencing attributes, or write this separately e.g. R1(attr1) references R2(some_key_attr). You can also use a schema diagram which identifies referential integrity constraints through arrows.
2.3 Normalization
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(s).
Tip. It is highly likely that the relations in the schema you come up with at the end of the ER-Relational mapping are already in 3NF. However, you still need to write the (non-trivial) FDs between the attributes of each of the relations you designed, and demonstrate that the relation is actually in 3NF by running the 3NF test on it.
2.4 Data Flow Diagrams
Provide a Context Diagram for this application. Provide also a Level-0 and three corresponding Level-1 DFDs. In the context diagram, all the input and output will be identified. In the Level-0 diagram, all major functions of the system and the named data stores are to be identified. In a Level-1 diagram, the main data flow for a major function will be described. Check for the consistency between the levels of DFDs. Check the consistency between the relational table schema and the data stores in the DFDs.
3 Implementation
The following describe the scope and requirements of the information system to be developed in MS Access:
3.1 Forms
Your system should include at least the following forms/screens that allow the user to add, remove, find and modify the records:
4
1. A user data entry form for an ICP to enter/delete/update details.
2. A form to enter/delete/update Content Creator?s details.
3. A form to enter/delete/update mobile phone user details.
4. A form for querying on the content materials. The content category information should be provided for the search purposes.
5. A form to subscribe content services to an ICP by a mobile phone user.
6. A form to query the overall usage of content materials over a period time by an ICP.
The user data entry form should include 4 command buttons: "Add Record", "Delete Record", "Find Record" and "Close Form".
At least one form should contain a sub form. One of the forms should include a combo box.
3.2 Reports
A report is used to query the database and create a printout to provide the query result and the summery information. The following set of reports is to be developed:
1. Content Creator Report: This report is to be generated for each Content Creator (user details appearing as page header), listing the details of its content materials and their content categories. A report should be able to print our report in multiple pages with proper header and footer on each page.
2. Individual Service History Report: For a given mobile phone user, print out its history in the order of the timestamps that the user has been requesting content services and list the details of the services provided including the costs charged to the mobile phone user.
3. Individual Content Material Usage Report: For a given content material, print out the records that show who have been interested in this content material and which ICPs were involved. The content material details should be given. The results of this report should be sorted according to the timestamps of records.
Every report should be self-contained. So after a report is printed out it will be self explanatory for what purpose this report is for, what time it is made, and who made this report.
3.3 Constraints
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.