Question

Database Management Sub-System Project SYou need to submit a project that represents a database management sub-system to solve a specific problem (e.g. university registration sub-system, hospital sub-system, supermarket, pharmacy, etc.). SYou need to submit a documentation, which includes the following: 1. Problem Definition 2. ER-Diagram 3. Tables Mapping 4. SQL Statements 5. Screenshots of forms to insert and read the data 6. Conclusion 7. References

Answer 1

Problem statement

The aim of this project is the development of a sample centralized relational Music store application. This application has to store information of customers and artists with their products. In this context the functionality is to update, remove and insert records for the different entities. The database is built for the clerks and the managers from a Music store. Customers are ordering by phone or by email. The clerk of the databases must be able to fulfill the wishes of the customer. These wishes include finding the right album and ordering this album.

The Core functionality is:

• Add, delete and update Customer information

• Add, delete, and update information about the album, artists and songs.

• Insert new and change a customer order. A customer order exists out of the customer information and the product information’s.

This documentation describes the development of this project and it includes: Architecture (the structure of the database), Implementation approach (the main parts: Database, Application and GUI), Functionality design (the structure of the program), GUI (Explanation and test scenarios), Technologies, Literature and Appendix.

ER diagram

The Entity Relationship Diagram (Figure 1) describes the entities Customer, Product, Artist, Track and Customerorder. Each entity has a primary key (underlined in Figure 1). A product (album) has the media type CD, DVD, Tape or Records and consists out of tracks (songs). The attributes of each entity are given in a circle with the primary keys underlined. The relationships such as orders, lists, produces and media tracks connect the entities in a structured and simple manner.

Track Artist produces Peoduct Customer Customeroder Filed Figure 1: ERD of the Music store Database

TABLES MAPPING

This definition is the base for creating the tables in the database. In general it gives the same information as the ERD but in a more specific way.

Track length mediatrack tracktitle productid Product produces Artist media price producttitle genre productied notes type instock productid orderid orders Customer orderid orderdate city street ap prefferedgenre email

SQL STATEMENTS

The creation of the tables is made by using the query language SQL. Two examples of the code are included below:

CREATE TABLE TRACK ( TRACKTITLE VARCHAR(100), LENGTH INTEGER, PRIMARY KEY (TRACKTITLE) )

CREATE TABLE CUSTOMER ( CUSTOMERID SERIAL, CITY VARCHAR(30), STREET VARCHAR(30), ZIP VARCHAR(30), FIRSTNAME VARCHAR(30), LASTNAME VARCHAR(30), PHONENUMBER INTEGER, EMAIL VARCHAR(50), PREFFEREDGENRE VARCHAR(30), PRIMARY KEY (CUSTOMERID) )

The application is implemented in Java. JDBC is used to connect to the database and to obtain the associated information. As an example the code to connect to the database is given in Figure 4. JDBC is also used to call SQL queries into the java code. This makes possible to access the database and create, update or view the data stored. Further explanation are provided in section related to the Functionality design.

try{
Class.forName("com.mysql.jdbc.Driver"); myConnection=DriverManager.getConnection(

"jdbc:mysql://123.156.5.73/dbpc-t6","username", "password"

); }

catch(Exception e){
System.out.println("Failed to get connection"); e.printStackTrace();

} }

SCREENSHOTS

DATABASE PROJECT MUSIC STORE 15 Acces to Music Team 6 Menu Custoer Product Settings Finances ษย nsert New Order Select Customer Customer Number Frst Name Last Name Street: View Al Orders Insert new Ouder Change Order Detals Delete Orde ip Code: Prettered Genre Select Product Product Number: Prodact Ttie Medi Type Genre Price: Release Dater In Stock Product Amount Total Product Price Search PModact Add Product Order Details e per piece Cancl Order Add Oder Figure 7: The menu item Insert New Order

14 DATABASE PROJECT MUSIC STORE Acces to MusicTeam 6 Product Order Settings Menu Custome View All Products View Insert new Preduct Produ:t Numbe Change Product Details Gente .50 Delete Produc Change Product Details Dekete Product Ext Figure 6: The menu item ‘View All Products,

DATABASE PROJECT 'MUSIC STORE 13 Acces to MusicTeam 6 Settings Finances Menu Prodkact Order Setings Finances Hep View All Customers Insert New Customer Change Customer Details Delete Customer RedNeck heaven udehude udehude 1000 1000 Leberie Comedy Bert 123455 8454 corp straat Musicals llem Change Customer t etals Delete Customer Exit Figure 5: The menu item 'View All Customer'

CONCLUSION

As stated in the introduction the minimum target at the beginning of this project was to show the core functionalities in a user friendly GUI. During the implementation the team reached their limits and succeeded in replacing these limits several times. This section will illustrate what the team reached and which problems occurred during the project. It will describe where the planning was realistic and will also give recommendations for similar or further projects.

The core functionality was reached in the following parts. It is possible to insert a new customer, change the details of a customer and delete a customer. Also the product part is implemented in the same way. A new product can be inserted, changed and deleted. Furthermore the order section is completely implemented. It is possible to fill in one order for several products. This order also can be changed and deleted.

REFRENCES

1. Afsarmanesh, H., Benabdelkader, A. and Hertzberger, L.O. (1998), A Flexible Approach to Information Sharing in Water Industries. In Proceedings of the International Conference On Information Technology, CIT'98, pages 135-142, Bhubaneswar, India, December 1998.

2. Arlow, Jim and Ila Neustadt (2002), UML and the Unified Process, Addison Wesley

3. Deitel and Deitel (2002), Java How to Program, 4th Ed, Prentice Hall

4. Deitel and Deitel (2002), Advanced Java How to program, Prentice Hall - Advanced Java 2

   Platform (Deitel, JDK 1.3, J2EE 1.2).

5. Frenkel, A., Afsarmanesh, H., Garita, C. and Hertzberger, L.O. (2000), Supporting

   information access rights and visibility levels in virtual enterprises...

6. Horstmann, Cay and Cornell, Gary, (2004), Core Java(TM) 2, Volume II--Advanced Features

   (7th Edition).

7. Guevara-Masis, V., Afsarmanesh, H. and Hertzberger, L.O. (2004), Ontology-based automatic

   data structure generation for collaborative networks

8. Silberschatz, Korth, and Sudarshan, (2002), Database System Concepts, 4th ed: McGraw-

   Hill.