System Architecture: Designing the Database Layer with Java, JDBC and SQL

1. System Architecture: Designing the Database Layer with Java, JDBC and SQL

2. Databases for StressFreev1.3 • StudentsDB - id, name, year, advisor, schedule, transcript • TranscriptsDB - student, myMajors, myMinor, • myCourseRecords, registrar • CoursesDB - id, title, distrib, sameAs, prerequisites, • myOfferings • OfferingsDB - id, section, labSection, semYear, capacity, • enrollment, myClassList, course, instructor, • timeSlot • SchedulesDB - student, approved, finished, numClasses, myClasses • InstructorsDB - name, myClasses, myAdvisees • TimeSlotsDB - id, days, startTime, endTime • Note: Some of these names are not the same as in your StressFreev1.3.zuml model. We will use the Poseidon-generated Java classes (with these name changes) that are now in the folder StressFreev1.3 at the course web site. Chapter 9: Architecting and designing software

3. SQL - Structured Query Language A good database software tool (reliable, easy to use, free, and well integrated with Java). • StressFree database creation, insertion, and queries are easily accomplished with simple SQL commands • Java/mySQL interface (JDBC and SQL Connector/J) allows us to implement the component of StressFree in Java. • The database component of StressFree will be part of the server side. • A good tutorial on SQL (but it’s long!) is at: http://www.bowdoin.edu/~yzhuang/download/John.Wiley.MySQL.pdf (Look at chapters 3 and 5 for starters.) • So let’s learn a little about SQL and its Java interface… Chapter 9: Architecting and designing software

4. SQL Overview • A Database is a collection of tables • It can be accessed by the ‘use’ SQL command. • Our database is called ‘csci260’ • Each table has rows of values of a certain type • E.g., ‘TimeSlotsDB’ is a table inside the csci260 database with rows of values of type ‘TimeSlot’. • A table is a collection of columns, one for each variable • It can be created and destroyed by the ‘create table’ and ‘drop table’ SQL commands. • Information in tables can be accessed, added, changed, removed, or combined with other tables using ‘select’, ‘insert’, and other SQL commands. Chapter 9: Architecting and designing software

5. Getting started: Connecting to the SQL Server • By command line -- our username is ‘csci260’ and our password is ‘regy’. • The host server is a separate machine outside the lab. • Only one database can be accessed at one time. Chapter 9: Architecting and designing software

6. Getting started: Connecting to the SQL Server • In Java we need to import the java.sql.* classes • These provide all the methods for manipulating SQL databases and tables. • This program is in the Eclipse folder ‘workspace260Reg’ at the course web site. Chapter 9: Architecting and designing software

7. Accessing a table in an SQL database • From the command line: use ‘show tables’ and ‘select’ commands Chapter 9: Architecting and designing software

8. Accessing the same table from a Java program – Note that the names are those of the TimeSlot class variables. Chapter 9: Architecting and designing software

9. Creating a new table in an SQL database (Java) The above lines simulate the SQL commands: mysql> CREATE TABLE myTable (id int, name text); mysql> INSERT INTO myTable (id, name) VALUES (1, ‘Yip’); Mysql> … Chapter 9: Architecting and designing software

10. Accessing and displaying data in a table (Java) Chapter 9: Architecting and designing software

11. Accessing and displaying data in a table (Java) Chapter 9: Architecting and designing software

12. Tables in the Database csci260: CoursesDB Chapter 9: Architecting and designing software

13. Tables in the Database csci260: InstructorsDB Chapter 9: Architecting and designing software

14. Tables in the Database csci260: OfferingsDB Chapter 9: Architecting and designing software

15. Tables in the Database csci260: StudentsDB Chapter 9: Architecting and designing software

16. Tables in the Database csci260: TranscriptsDB Chapter 9: Architecting and designing software

17. System Architecture:Package Design Principles • Package functionally cohesive groups E.g., Group Student, Transcript, Schedule E.g., Client classes vs Server classes (as in the KnockKnock system) E.g., Database access classes • Package functionally related interfaces E.g., StressFree GUI Relational cohesion = Low cohesion indicates poor packaging. Number of Relations Number of Classes Chapter 9: Architecting and designing software

18. StressFreev1.3 Package Design Chapter 9: Architecting and designing software

19. System Architecture:Deployment Diagrams • Identify software and hardware platforms where the system will run • Each box is a hardware/OS node in the system • Links between boxes show communication paths and protocols • Files and other artifacts can appear inside the boxes • Boxes can be nested Chapter 9: Architecting and designing software

20. StressFreev1.3 System Architecture Chapter 9: Architecting and designing software