Database Design for Storing Certificate

Perform the following tasks:

Part I - Database Design

In the previous homework, the certificate you generated is only self-signed. In other words, the certificate chains back to itself. In this part of the homework, you are to design a relational database scheme in the form of a ER diagram that can store general x.509 certificate.

Here is the list of requirement for the database schema:

• Must be able to store both the base64 encrypted certficate and the DER encrypted certificate.
• Must be able to store "leaf" certificate, and any level of sub-CAs, and root CA relationship.
• Must provide ways of querying individual fields of a certificate (i.e. store certificate field separately)
• Must be maintable. Thus, schema should support add, delete, and "modify" with consistence of data.
• Must be able to support all x.509 version 3 feature (such as extension, etc). http://www.e-timestamp.com/reference/rfc2459.htm

Part II - MySQL Schema

Use the design in Part I and create the corresponding MySQL schema. The schema should contain any DDL and DML SQL statements needed to create the tables as well as populating the initial data set. You could modify your previous homework to generate certificates that have sub-CAs.

NOTE:

What to handin:

• Design document (ER diagram + detail description of the design) in MS-Word format of Part I.
• MySQL schema of Part II on CD as well as printed copy.
• This is a group homework, use the same group members as your project group. Work with each other early.
• Homework is based on complete correctness, so MAKE SURE everything is neat and correct.
• Use a good word processor to writeup all the written assignments. Print them out and handin in class.
• Handin DETAIL running instructions and the actual program (source + class files + html file + h + c files, etc). Print instruction and program listing out, and hand them in with the computer diskette or CD. (I prefer CDs.)
• USE an envelope and put everything in it.
• No late assignment will be accepted
• DO NOT EMAIL ANY assignment to me. Handin in class
• Points will be taken off if you violate any of the above.
• If you do not start early, you probably cannot finish it ... START EARLY.